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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tarun912
Helper IV
Helper IV

Rolling 12 months data

Hi @amitchandak , 

I am using this measure for getting 12 months back data on my line chart but facing some issues in it.

Rolling 12 Graph 2 =

VAR _MAX = MAXX(ALLSELECTED('Date'),'Date'[Date])
VAR _MIN = DATE(YEAR(_MAX)-1, MONTH (_MAX), DAY(_MAX))
VAR Result =
CALCULATE(
SUM(TableName[SCORE1]),
FILTER(ALL('Date'),
 
'Date'[Date]>= _MIN && 'Date'[Date] <= _MAX

)
)
RETURN
Result

But, in this measure where i have used SCORE1 , basically my SCORE1 is a measure and I was unable to use it there as a measure so, i have to convert it into a column SCORE1 , but still it is not giving me the desired result.

Can you please help me ?
Thanks !
1 ACCEPTED SOLUTION

@tarun912 

 

I see your requirement now, to explain the dax to get last 12 month, please see this example pbix. And change the return expression to Count().

 

Paul Zheng _ Community Support Team
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

10 REPLIES 10
PradeepTh
Frequent Visitor

Hi,

I am also facing the same issue. So, please share the link of above example: "example.pbix" again.

 

Regards,

Pradeep

V-pazhen-msft
Community Support
Community Support

@tarun912 

How did you change [Score1] to a column? Can you provide the dax. Usually the dax for measure and column is different.

Please elaborate more with a sample pbix.

 

Paul Zheng _ Community Support Team
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

@tarun912 check this post, you can tweak as per your business needs.

 

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



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.

@tarun912 I also have a solution at this post, check it out.



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.

Hi @parry2k , 

Actually my requirement is somewhat complex , these suggestions didn't help me.

Thanks !

@tarun912 than you have to take time to explain your problem with sample data and expected output, there is no way to understand what is the complexity in your problem give the original post.

 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



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.

Hi @parry2k ,

https://docs.google.com/spreadsheets/d/1lQI0l-Ahz30XkgPx7jABx_Cy8V9usJb6AJzelZX04PI/edit?usp=sharing

Here's my sample dataset.

So, I basically have a dataset that contains:
A- is a primary key containing multiple iDs
B - Date stamp format (mm/dd/yy)
C - contains 2 types of yes or no option
D- contains 4 different products (toy, train, bus, car)

In addition, I have created a TABLE OF FECHAs in POWER BI.

Basicall my requirement is , I've done a slicer of my date table in my report, so I want every time I select a date from my slicer, then I want to show the count of my iDs (from column A) from a year ago in a line chart. (Also, due to some UI restriction I can't use relative slicer, I only have the option to select a single date from date slicer)
For example: If I select 5 May 2020 in the TABLE DATE segmentation, then I want to display the ID count (from column A) in a line chart, from MAY 2019 to APRIL 2020 (Also, due to some UI constraint, I don't have the option to use relative date segmentation , I only have the option to use normal date segmentation)

I hope you understand my problem statement.
Thank you!



Hi @parry2k ,

https://docs.google.com/spreadsheets/d/1lQI0l-Ahz30XkgPx7jABx_Cy8V9usJb6AJzelZX04PI/edit?usp=sharing

Here's my sample dataset.

So, I basically have a dataset that contains:
A- is a primary key containing multiple iDs
B - Date stamp format (mm/dd/yy)
C - contains 2 types of yes or no option
D- contains 4 different products (toy, train, bus, car)

In addition, I have created a TABLE OF FECHAs in POWER BI.

Basicall my requirement is , I've done a slicer of my date table in my report, so I want every time I select a date from my slicer, then I want to show the count of my iDs (from column A) from a year ago in a line chart. (Also, due to some UI restriction I can't use relative slicer, I only have the option to select a single date from date slicer)
For example: If I select 5 May 2020 in the TABLE DATE segmentation, then I want to display the ID count (from column A) in a line chart, from MAY 2019 to APRIL 2020 (Also, due to some UI constraint, I don't have the option to use relative date segmentation , I only have the option to use normal date segmentation)

I hope you understand my problem statement.
Thank you!



Hi @parry2k , 

https://docs.google.com/spreadsheets/d/1lQI0l-Ahz30XkgPx7jABx_Cy8V9usJb6AJzelZX04PI/edit?usp=sharing

This is my sample dataset.

So, basically I have a dataset that contains :
A- it is a primary key contains several IDs
B - Date time stamp (mm/dd/yyyy)  format 
C - this contains 2 types of option yes or no 
D- contains 4 different products  (toy , train , bus , car ) 

Also, I have made a DATE TABLE in POWER BI.

Basicall my requirement is , I have made a slicer from my DATE TABLE in my report , so I want whenever i select a date from my slicer, then I want to display the count of my IDs (from column A ) of one year back on a line chart. (Also, due to some UI Constraint i can't use relative slicer , i just have the option to select a single date from the DATE TABLE slicer)
For eg: If I select 5 may 2020 in DATE TABLE slicer, then I want to display the count of IDs ( from column A ) in a line chart , from MAY 2019 to APRIL 2020 (Also, due to some UI constraint , I don't have the option of using the relative date slicer, I just have the option of using the normal date slicer)

I hope you understand my problem statement.
Thanks ! 



@tarun912 

 

I see your requirement now, to explain the dax to get last 12 month, please see this example pbix. And change the return expression to Count().

 

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

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

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