topic Re: Quartal vs last year quartal comparison in DAX Commands and Tips

Quartal vs last year quartal comparison

jordanesqu — Mon, 24 Feb 2020 15:51:51 GMT

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

Re: Quartal vs last year quartal comparison

Pragati11 — Mon, 24 Feb 2020 17:33:37 GMT

Hi jordanesqu ,

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

Thanks,

Pragati

Re: Quartal vs last year quartal comparison

Anonymous — Mon, 24 Feb 2020 17:36:10 GMT

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

Re: Quartal vs last year quartal comparison

jordanesqu — Tue, 25 Feb 2020 08:50:05 GMT

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

Re: Quartal vs last year quartal comparison

Anonymous — Tue, 25 Feb 2020 08:58:28 GMT

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

Re: Quartal vs last year quartal comparison

jordanesqu — Tue, 25 Feb 2020 09:01:37 GMT

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

Re: Quartal vs last year quartal comparison

jordanesqu — Tue, 25 Feb 2020 09:10:38 GMT

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

Re: Quartal vs last year quartal comparison

Anonymous — Tue, 25 Feb 2020 09:22:08 GMT

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

Re: Quartal vs last year quartal comparison

jordanesqu — Tue, 25 Feb 2020 10:53:01 GMT

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!