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
smignogna
Frequent Visitor

Trailing 3 months

Do you know how to create a dynamic measure for trailing 3 months, based upon a slicer date range?   For example, in November, it would calculate Aug, Sept, Oct.....     and in Dec, it would calculate Sept, Oct, Nov.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Sure, it's going to be a variation on my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Essentially, create a VAR that is MAX([Month]) - 1. This is your selected month and it should be numeric. Then you create another VAR that is that variable minus 2. Then it is a simple matter of filtering ALL of your table where the numeric month is between those two values.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @smignogna

Here is a test pbix  you may refer to

 

create a calendar date table, don't connect this date table to your table, this calendar date table would automatically generate the date according to your tables.

Table = CALENDARAUTO()

create "year" and "month" column inside this calendar date table

year = YEAR([Date])

month = MONTH([Date])

Then add [year] and [month] column in the slicers

 

Next create measures in your table

Measure =
CALCULATE (
    SUM ( Sheet1[values] ),
    FILTER (
        Sheet1,
        YEAR ( [date] ) = SELECTEDVALUE ( 'Table'[year] )
            && MONTH ( [date] ) < SELECTEDVALUE ( 'Table'[month] )
            && MONTH ( [date] )
                >= SELECTEDVALUE ( 'Table'[month] ) - 3
    )
)

19.png

20.png

 

Best Regards

Maggie

 

 

Greg_Deckler
Super User
Super User

Sure, it's going to be a variation on my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Essentially, create a VAR that is MAX([Month]) - 1. This is your selected month and it should be numeric. Then you create another VAR that is that variable minus 2. Then it is a simple matter of filtering ALL of your table where the numeric month is between those two values.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you so much Greg!  This worked perfectly!

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.

Top Solution Authors
Top Kudoed Authors