Trouble with 12 month rolling with 2 period (example)

[yboulinguez]

Hi community, 

Im hard stuck on hudge problem, i would like to build à time line chart based on 12 rolling month mesure and 12 rolling month mesure parallel periode n-1. 

I have build an example for illustrate my problem : 

I build something like that :  Link

V1 :

Select end date of period and we have 2 x 12 rolling month  mesure.

Have on graph 2 period of 12 month for compare saisonality. 

 Problem : The order of month is wrong, this always start à january and i want a dynamic order based on selected month. 

V2 :

Select month and year and we have 12 rolling month mesure 

Problem : With this method, Month order is ok because he is based on month + year. But we cant supperpose 2 mesure because is based on monthYear

Somedy have a solution for me ? 

Im desesperate. 

Thanks

[MFelix]

Hi @yboulinguez,

Check this post on SQLJason,

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

Can help you achieve what you need.

If any assistance is needed to get the final result please tell me and I will try to help in any way I can.

Regards,

MFelix

[yboulinguez]

Hello @MFelix

Thanks for your answer, it can be an alternativ solution

But I want to compare saisonality for 12 rolling month on 2 distinct period, so my 2 line must be superpose, and for do that i can't use MonthYear in axis. 

Have you another idea ? 🙂

Regards

[yboulinguez]

up 

[MFelix]

Hi @yboulinguez

I have found a solution for this problem, however yesterday I have tried to publish my solution and my post was deleted from the forum, don't understand why.

I will try to redo the post later.

Sorry for taking so long to answer.

Regards,

MFelix

[yboulinguez]

Hello, Thanks for your solution, but with this date table dont work with date slicer.

Effectivly this graph must be compose with date Max selector so the 12 month rolling are dynamic. 

The only solution thats i found is superpose 2 graph. 

Thanks.

[MFelix]

Hi @yboulinguez,

I have copied the visual incorrectly, if you put the measures that I calculate in the new table on your V2 chart it will be dinamic.

Please check it and get back to me.

Regards,

MFelix

[yboulinguez]

Oh, thanks, this is work very well. 

Last question : if i want filter and only show result for 1 and 2 value of EmpID, 

Thank

Yoan 

[MFelix]

Hi @yboulinguez,

Try the following. Add a table with the following code:

Summary Table = 
ADDCOLUMNS (
    DISTINCT ( Dates[Year Month Number] );
    "CurrentYear"; Employees[Number of Employees];
    "PreviousYear"; CALCULATE ( [Number of Employees]; DATEADD ( Dates[Date]; -1; YEAR ) );
    "Date"; EOMONTH (
        DATE ( LEFT ( Dates[Year Month Number]; 4 ); RIGHT ( Dates[Year Month Number]; LEN ( Dates[Year Month Number] ) - 4 ); 1 );
        0
    )
)

Then add the following measures:

CY Employees Joining (Last 12 Months) = 
CALCULATE(
    SUM('Summary Table'[CurrentYear]);
    DATESINPERIOD( Dates[Date]; MAX( DatesFilter[Date] ); -12; MONTH );
    VALUES( Dates[Date] )
)

PY Employees Joining (Last 12 Months) = 
CALCULATE(
    SUM('Summary Table'[PreviousYear]);
    DATESINPERIOD( Dates[Date]; MAX( DatesFilter[Date] ); -12; MONTH );
    VALUES( Dates[Date] )
)

Then just place them on your visual.

Not sure if this is what you need since it's adding a table, but to what I can check when adding the rolling average the context is lost for getting the previous year values. The new table only has 48 rows, so not a big number of information could work.

Check PBIX attach

Regards,

MFelix

[pdacheva]

Hi MFelix,

I tried your solution, but I cannot get the PreviousYear measure calculated in the Summary Table. I get NULL for these column. Tried with your implementation and also with SAMEPERIODLASTYEAR() function, but I get nothing for these coilumns.

My code is :

Summary Table =

ADDCOLUMNS (

DISTINCT ('Calendar'[YearMonthNumber] ),

"Date", EOMONTH (

DATE ( LEFT ( 'Calendar'[YearMonthNumber], 4 ), RIGHT ( 'Calendar'[YearMonthNumber], LEN ( 'Calendar'[YearMonthNumber] ) - 4 ), 1 ),

0

),

"CurrentYear GP Sales", [Total GP Sales],

    "CurrentYear NP Sales", [Total NP Sales],

    "PreviousYear GP Sales", CALCULATE ([Total GP Sales], DATEADD ( 'Calendar'[Date], -1, YEAR ) ),

    "PreviousYear NP Sales", CALCULATE ( [Total NP Sales], DATEADD ( 'Calendar'[Date], -1, YEAR ) ),

)

It seems as if it can not calculate the Current Row Date to properly get the Previous Year. My meausre is sum, I see that you just count rows - not sure if this is the issue. I have two years data, so for the Last date, I'm expecting to get the amounts for the first one in these columns. 

I have the exact same requirement as described by @yboulinguez, and I cannot get any sort/calculation to get the desired result. Is there a step/trick I'm missing in the implementation?

Thanks!

[MFelix]

Hi @pdacheva ,

On the answer I gave previously the objective was to get the active number of employees so the count rows return that value.

What is the details of your data? Can you share a sample?

If information is sensitive you can prepare a mockupdata or send the link trough private message.

Regards,

MFelix

[pdacheva]

Hi @MFelix ,

I'm sorry, I can't share the data, but I basically have Sales data with daily granularity (in Sales table). Therefore my calculation would be SUM(Sales) per each year/month. My Calendar table is basically a Date table with all dates between MIN and MAX date from the Sales table. I need to show Last 12 Months against Previous 12 Months Sales on the same axis and the report to be able to interact with the Date slicer. I need to achieve exactly what you have in your pbix, but instead of COUNTROWS, my measure is SUM. Currently, when I put my CY / LY sales I have this issue with the axis as in the attached image.

[MFelix]

Hi @pdacheva ,

Check the post below since it has the same issue as your if you need any further assistance please tell.

https://community.powerbi.com/t5/Desktop/Rolling-13-months-DAX/m-p/663832#M319130

Regards,

MFelix

[pdacheva]

Hi @MFelix ,

Thank you! I'll try to adapt the solution to my model!

Best,

Petya