topic R12 calculation is different than date filter on graph in DAX Commands and Tips

R12 calculation is different than date filter on graph

Anonymous — Mon, 04 Mar 2024 21:45:58 GMT

Hello, I have a measure that references a date table to do a R12 calculation. The issue I'm having is that the R12 measure comes up with a different number than when I just put a relative date filter for the past 12 calendar months on the graph. I need the number that the graph shows with the relative date filter to be the same as the measure itself because I am showing a table underneath with the R12, monthly, and weekly scores. Therefore I can't put a relative date filter on the entire table. Is there a calculation I can do that will show the last 12 calendar months of data? Below are the measures I have currently.

Date Table =

VAR MinYear = YEAR( MIN( Query1[Date]))

VAR MaxYear = YEAR( MAX( Query1[Date]))

RETURN

ADDCOLUMNS(

FILTER(

CALENDARAUTO(),

AND(

YEAR( [Date] ) >= MinYear,

YEAR( [Date] ) <= MaxYear

)

"Year", YEAR( [Date] ),

"Month Number", MONTH( [Date] ),

"Month Name", FORMAT( [Date], "mmm" ),

"Quater", "Q" & INT( FORMAT( [Date], "q"))

R12 measure =

CALCULATE([DE NPS], DATESINPERIOD( 'Date Table'[Date], MAX('Date Table'[Date]), -12, MONTH))

Re: R12 calculation is different than date filter on graph

Anonymous — Tue, 05 Mar 2024 02:28:11 GMT

Hi Anonymous

Could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: R12 calculation is different than date filter on graph

Anonymous — Fri, 08 Mar 2024 16:45:15 GMT

I found that using the ALL function solved the issue!

Re: R12 calculation is different than date filter on graph

Anonymous — Mon, 11 Mar 2024 01:11:22 GMT

Hi Anonymous

Congratulations! It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.