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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

R12 calculation is different than date filter on graph

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))
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I found that using the ALL function solved the issue!

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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.

Anonymous
Not applicable

I found that using the ALL function solved the issue!

Anonymous
Not applicable

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.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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