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
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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