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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
yybi123
Helper I
Helper I

Last "x" months sum measure not showing value in visual

hi everyone, I have a dataset of date, country, volume and a start date. What I want to achieve is to get a sum of last 3months volume base on start date. 

I created this measure when I put it in my visual is showing blank. My visual - bar chart require the Country as the X axis. and Y axis as the value.

 

Measure:

Last 3mths Vol = CALCULATE(SUM('Table'[Volume]),DATEADD('Table'[Start Date],-3,MONTH))


by right for country A it will be the sum of Dec 2020 - Feb 2021.

Month Country Volume Start Date

01/01/21A501/03/21
01/02/21A1001/03/21
01/03/21A5001/03/21
01/10/20A501/03/21
01/11/20A601/03/21
01/01/21B701/01/21
01/02/21B801/01/21
01/10/20B701/01/21
01/12/20B201/01/21
 
1 ACCEPTED SOLUTION

@yybi123 , Try this, this should use start date country wise, of the country is in context

Rolling 3 = CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX(Table[Start Date]),-3,MONTH))

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@yybi123 , with help from a date table

 

Rolling 3 = CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3= CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

I need the last 3 months sum base on the start date. Each country might have a different start date.
For example , A will start on Mar 2021 and B start on Jan 2021.

So the last 3mths sum from start date for A will be sum of Dec 2020-Feb 2021 and B will be Oct 2020-Dec 2020.

@yybi123 , Try this, this should use start date country wise, of the country is in context

Rolling 3 = CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX(Table[Start Date]),-3,MONTH))

I tried this 

CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Calendar'[Date],MAX('Table'[Start Date]),-3,MONTH)) .
 
Is having a value however, it included the start month ie if start date is 01/03/2021 the measure sum up March2021, Feb2021 and Jan2021 instead of Feb2021, Jan2021 and Dec2020. Any idea?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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