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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Xboraxe
Frequent Visitor

Dynamic Rolling 3 Month Sum

Hi all,

Having the below table, I want to be able to filter by month and have the 3 month rolling sum automatically update as a card visual.

 

monthlead_cnttotal_3month
2023120600
202311100800
202310500900
202309200700
202308200600
202307300400
202306100100

 

I'm currently using the below DAX but for some reason the sum is only accurate when I filter for October (202310). When I filter for other months the card doesn't automatically sum the respective previous 3 months. For instance, 202311 shows 100 and for 202312 shows 0, both incorrect.

 

total_3month = 
CALCULATE(
    SUM(table[lead_cnt]),
    DATESINPERIOD(date_table[mmm],LASTDATE(date_table[mmm]),-3,month))    

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

 

Jihwan_Kim_1-1704945105909.png

 

 

Jihwan_Kim_0-1704945084421.png

 

 

total 3 months measure: = 
CALCULATE (
    [lead cnt measure:],
    DATESINPERIOD ( date_table[Date], LASTDATE ( date_table[Date] ), -3, MONTH )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

 

Jihwan_Kim_1-1704945105909.png

 

 

Jihwan_Kim_0-1704945084421.png

 

 

total 3 months measure: = 
CALCULATE (
    [lead cnt measure:],
    DATESINPERIOD ( date_table[Date], LASTDATE ( date_table[Date] ), -3, MONTH )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you, this is exactly what I needed.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors