Calculation on latest period

[myriam_ouan]

Hi power BI community,

I am a bit new to this environment and I'd like to know whether it's possible to make the following calculations.

My goal is to create a generic power BI file that I can feed with data from any time interval and have it display :

- latest quarter results (1)

- same quarter last year results (2)

- variation between (1) and (2)

- total year to end of latest quarter results (3)

- total last year to end of same quarter last year results (4)

- variation between (3) and (4)

Here is the structure of my data : 

- amounts column 

- date column in format dd/mm/yyyy that allows power BI to create a hierarchy 

The idea is to use the same power BI file every quarter and just change the source but have all the parameters ready for fresh data. 

For the first one (1), I tried the following formula : 

CALCULATE ( SUM ('Table'[Amounts]), 'Table'[Period].[Quarter] = MAX ('Table'[Period].[Quarter]) ) )

But I noticed that MAX does not work in a filter expression. I also tried the same formula with LASTNONBLANK or LASTDATE but same problem. 

Has anybody done something similar or would know if it is even possible ?

Many thanks. 

M

[Anonymous · Accepted solution]

It does not work because it can't work. But what you're doing will not do what you want, even if you get the syntax right. That's because you should never use the automatically generated date hierarchies in any model; just forget they exist and you'll thank me later. They will lead you astray. Please do yourself a favour and create a proper calendar in your model first. Then, it'll be much, much easier. If you want to know how to properly design models, you can read this: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

If you have a fact table and a proper calendar, then you can get the the amount for the latest quarter like this:

[Total Amount] = SUM( FactTable[Amount] )

[Amount Latest Quarter] =
// Bear in mind that QuarterID must uniquely
// identify the quarter in all of the years
// you have in Calendar. So, for instance, it
// should be an int of the form YYYYQQ, like
// 202004.
var __latestQuarterID = year(today()) * 100 + quarter(today())
var __latestQuarterAmount = 
    calculate(
        [Total Amount],
        Calendar[QuarterID] = __latestQuarterID,
        removefilters( 'Calendar' )
    )
return
    __latestQuarterAmount

The above is relative only to TODAY. If you want to have the amount for the quarter to which the latest visible date in the context belongs, you'd use this:

[Amount in Last Visible Quarter] =
// Same remarks as above...
var __maxQuarter = max( Calendar[QuarterID] )
var __maxQuarterAmount = 
    calculate(
        [Total Amount],
        Calendar[QuarterID] = __maxQuarter,
        removefilters( 'Calendar' )
    )
return
    __maxQuarterAmount

If you want to get the amount for the quarter to which the latest date belongs but only up to this date, then you'd use this code:

[Amount in Quarter Up to Last Visible Date] =
// Same remarks as above...
var __maxDate = max( Calendar[Date] )
var __maxQuarter = max( Calendar[QuarterID] )
var __maxQuarterAmount = 
    calculate(
        [Total Amount],
        Calendar[QuarterID] = __maxQuarter,
        Calendar[Date] <= __maxDate,
        removefilters( 'Calendar' )
    )
return
    __maxQuarterAmount

[amitchandak]

@myriam_ouan , assuming you date is detected as date in dd/mm/yyyy format. else refer https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

For this qtr vs last you can use time intelligence

example

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))

if qtr does not start from 1,4,7 and 10. then have these column in date table

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH) //choose year end date in place of 4/30

have measures like

This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Month No ] <=max([Qtr Month No ])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Month No ] <=max([Qtr Month No ])))

This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Day] <=max([Qtr Day])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1