Static Time Intelligence

[Chitemerere]

I need to put static point in time reporting (time intelligence) measures in a dashboard or a table, i,e. they are not based on filter selection. The screenshot below from another BI tool i am migrating from shows the concept.

Example Output

YTD = Year to Date and LYYTD = Last year Year to Date.  The above are calculated as static measures and no filters are applied.  I notice that the various time intelligence measures calculated using DAX in Power BI need to apply date filters to work.  In a nut shell, say i have data from April 2018 to March 2020 and need Sum (Quantity) for March only or Sum (Quantity) for the previous month i.e. February only.  It goes on to say Sum (Quantity) for two months ago.  It goes with other measures like year to date, moving annual total etc.  These should be calculated with no input/use of filters.

For example, in my last BI tool, calculation for example LYYTD = Sum({$<$(vSetLYYTD)>} QTY) where vSetLYYTD is a variable based on the calendar. QTY is quantity and is shown in the sample data. I show below the variables:

Let vSetYTD = 'PeriodID = {"<=' & Chr(36) & '(=Max(PeriodID))"},' & Chr(10) &

'Year = {' & Chr(36) & '(=Max(Year))},' & Chr(10) &

'Quarter = ,' & Chr(10) &

'Period = ,' & Chr(10) &

'Month = ';

Let vSetLYYTD = 'PeriodID = {"<=' & Chr(36) & '(=Max(PeriodID) - 12)"},' & Chr(10) &

'Year = {' & Chr(36) & '(=Max(Year)-1)},' & Chr(10) &

'Quarter = ,' & Chr(10) &

'Period = ,' & Chr(10) &

'Month = ';

etc

PeriodID and QuarterID are calculated in the calendar and the fact table based on date as follows:

Year([Date Dispensed]) * 12 + Month([Date Dispensed]) as PeriodID, 

 AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID], 

These variables are loaded into the model when loading the rest of the data.

I have sample data below:

Sample Data 

How can i accomplish the calculation of static measures in Power BI.  These are important in some cases like on a Dashboard where you do not want any selections bu

Thanking you in advance and best regards.

Chris

[amitchandak]

@Chitemerere 

You should try to use time intelligence with date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Chitemerere]

Thanks, once again, i have tried to implement the first suggested solution, however, this does not work without a selection in the date table.  As pointed in my post above and the given example, the solution i am looking for is independent of selections in the date table.

Best regards,

Chris

[Chitemerere]

Thank you very much, most appreciated. Let me work on it and will revert once i am through.

Regards,

Chris

[amitchandak · Accepted solution]

Another way is create Rank on Year, Qtr and Month and use that to find current prior

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank]))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank])-1)

Year can work without rank.

Refer, this week example

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Chitemerere]

Again, thanks a lot.  I wil work it through and come back to you.

Regards,

Chris

[parry2k]

@Chitemerere wow there is a lot reading. seems like these are basi time intelligence calculations and there are tons of blog posts on it. Did you checked /research on what is already available and then can you come back with specific queston where you couldn't find the solution. 

[Chitemerere]

Thank you very much for your prompt response.  What i am presenting here i dont think is available in Power BI.  I have given specific examples i have been using in QlikView.  Below is a Power BI example:

This example has a date column which is different from what i presented using another BI tool, it does use a date columin

Regards,

Chris