Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rolling 12 Months and Prior Year Rolling 12 Months in the same visual

Hi All,

 

I am trying to show Current year rolling 12 month and Prior year rolling 12 month measures as two lines on the same chart. When I put them in the Line Visual using Month (without Year) on the Axis it starts from Jan as expected and when I use Month&Year I get lines one after another like below. I have seen a similar thread related to this topic but didn't find any solution, Please suggest if there is any work around.

 

Thanks in advance

 

Using Month & YearUsing Month & YearUsing Month OnlyUsing Month OnlyActual numbersActual numbersExpected OutputExpected Output

 

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

What is your measure for Prior year rolling 12 month ?

Based on my research, your measure should achieve below data:

1.PNG

You could also download my pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-danhe-msft ,

 

Sorry for the late response. I see this is marked as solved but I would still like to post my measures which didn't give me what I want:

 

Rolling 12 Months = 

VAR currDate =
MAX (vw_DIM_Month[FirstofEveryMonth] )
RETURN
CALCULATE (
[Total DAR],
FILTER (
ALLSELECTED ( vw_DIM_Month ),
[FirstofEveryMonth]
> DATE ( YEAR ( currDate )-1, MONTH ( currDate ), DAY ( currDate ) )
&& [FirstofEveryMonth] <= currDate
)
)
 
Prior Year Rolling 12 Months =
VAR currDate =
MAX ( vw_DIM_Month[FirstofEveryMonth] )
RETURN
CALCULATE (
[Total DAR],
FILTER (
ALLSELECTED ( vw_DIM_Month ),
[FirstofEveryMonth]
> DATE( YEAR ( currDate ) - 2, MONTH ( currDate ), DAY ( currDate ) )
&& [FirstofEveryMonth]
<= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
)
)
 
Thanks
 
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

What is your measure for Prior year rolling 12 month ?

Based on my research, your measure should achieve below data:

1.PNG

You could also download my pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k
Super User
Super User

@Anonymous seems like your measures are not workign as expected. In the table it should show previous year rolling measure next to current year rolling and that is the case you are not getting expected result.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.