Rolling 12 months data

[tarun912]

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 !

[PradeepTh]

Hi,

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

Regards,

Pradeep

[V-pazhen-msft]

@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]

@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!

[parry2k]

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

[tarun912]

Hi @parry2k , 

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

Thanks !

[parry2k]

@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

[Syndicate_Admin]

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!

[Syndicate_Admin]

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!

[tarun912]

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 ! 

[V-pazhen-msft · 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.