Custom Date Range

[Capt.Ragnar]

I need the range of days in a table (where a single month has been selected ) to return the 14th of the prior month through month end of the selected month.

I am using the BasicCalendarUS table from the Azure Marketplace as my date table for reference if it aids in a solution.



Any help is appreciated.

 

[Matt Allington]

How about posting a sample workbook. This is fairly straightforward but it will help to know your data model. Is the 14th a significant date each month? If so you should probably build a custom calendar table.

 

[Capt.Ragnar]

How about posting a sample workbook. This is fairly straightforward but it will help to know your data model. Is the 14th a significant date each month? If so you should probably build a custom calendar table.

I'm open to either, but the logic on a table where dates are not unique relating back to source data would seem difficult.

I provided a sample wkbk here. http://1drv.ms/1UH5jPu

Thanks again.

 

[Matt Allington]

Here is a working solution https://dl.dropboxusercontent.com/u/30711565/45DayLookBack_TestWkBk2.xlsx

It uses a disconnected dates table that contains the start and end dates of each month. The user selects one of the months and then an if statement checks if each date in your data table is in that month or >=14th of the prior month. If it is, it displays the answer

=if(countrows(FILTER (        DailyBalance,
        DailyBalance[Date] >= 
        	DATE ( 
            	YEAR ( LASTDATE(Selector[Last Day] )), 
            	MONTH ( LASTDATE(Selector[Last Day])) - 1 ,
            	14 )
             && 
        DailyBalance[Date] <=  LASTDATE(Selector[Last Day] )
    ))>0,[Sum of Balance])

 

[Capt.Ragnar]

That is legit.

Great stuff. I'm getting better but now where close to what you have here.

Thank you.

DA

 

[Capt.Ragnar]

Help on this line if you can:

,[Sum of Balance])

I used a table in the example but in the actual data that field is a Calculated Field in another table. Is it possible to reference the calculated field in the formula you have above or just create a queried table that replicates what I used in the sample workbook?

 

[Matt Allington]

[Sum of Balance] is an implicit calculated formula (I will call it a measure). This is not good practice but fine for demo workbooks like this. You should write your own measure that does the calculation you need. It may or may not work if it is in another table - it depends on the shape of your data model. Filter propagation is a deep topic and you really need to understand it if you want to be a DAX ninja. I cover this extensively in my book in Chapters 5, 8 and 9 (Learn to Write DAX - the book for all Excel users)

If you can post a picture of your full data model (showing table names) and which table has your measure, I can help you understand if it will work. Or you can just test it.