Quartal vs last year quartal comparison

[jordanesqu]

Dear All,

I am trying to build a quartal to quartal comparison eg:

Q1 2020 (Jan+Feb) vs Q1 2019 (Jan+Feb+Mar)

I ve tried with SAMEPERIODLASTYEAR or PREVIOUSQARTER or DATEADD but as a result I always end up with:

Q1 2020 (Jan+Feb) vs Q1 2019 (Jan+Feb) which means I miss in prevoius period the month that is about to come (Mar).

My raw data are on monthly basis. I have created a separate Calendar Table with Date,Year;Month,Quartal.

Thanks

jordan

[Anonymous]

You really want to compare to one more month from the previous year? Everything is, of course, doable but... Why do you want to compare 2 periods out of which one is fully contained in the past and the other has not yet finished? Do you really want to do that?

Best
D

[jordanesqu]

I know that is it not something common, but thats one of the measures that our guys looking at (they want to know how far it is to reach previus quarter). We have that view in Bex or Excel but I am having troubles to build it in PowerBi. I do not want also to spend an enormous amount of time to build something super complicated but if there is a reasonable solution would be happy to impement that.

thanks

[Pragati11]

Hi @jordanesqu ,

Can you kindly put a screenshot of sample data and the desired output?

Thanks,

Pragati

[jordanesqu]

Hi,

Data structure looks like that: Date in one column and values in another (what I show is a measure "Area/Total"). Ouput is simple table or matrix.

thanks

[Anonymous]

Yeah... This picture is almost of no informative value. Can you please show us the formula for the calculation of the PY value? I suspect that you are using the Date column from the above picture instead of a proper Date table (Calendar)....

If you want to know how time-intel is and should be handled in Power BI, please have a look at this: https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D

[jordanesqu]

I have a separate Table (Calendar) for that. On a monthly basis everything works correctly (Act vs LY comparison) but on quarterly basis I am missing this one month from previous period... 

Area LY = CALCULATE(SUM('PBI_ZCAC_Dash:GL | Total 2 0'[Area]);SAMEPERIODLASTYEAR('DIM Calendar'[Date]))

thanks

[Anonymous · Accepted solution]

First, each Calendar should start on the first day of a year and end on the last day of a year. Second, such a calendar must be marked as a DATE TABLE in the model. Then and then only will it be suitable for time-intel calculations. Is your calendar like this?

Your formula is correct for the calculation you are describing. If your calendar is correct, then the formula is correct as well.

Das ist alles, was ich zu Dir sagen kann 🙂

I can even create a quick model with a Calendar and some fact table to demonstrate that the measure indeed will return what you want.

Best
D

[jordanesqu]

I have my Calendar modified so it shows all periods for 2020 and now I can see Q vs LY Quartal works.

thanks for a tip and help with that!