cancel
Showing results for
Did you mean:
Highlighted Helper I

## Calculating and aggregating Previous Month(s) data when gaps in data are present

What I am doing is diplaying the current months \$/Runtime and the \$/Runtime for the last 3 (active) months. What is happening is the totals are including the zero data months if they exist. I have three tables Runtime, Costs, and Dates (date table) as seen below(excel example). To the right of the pivot table in the Prev3 column is how i am trying to get power bi to output the data. The yellow highligted areas are how power bi is outputting the data.

The DAX I have is right here. Additionally I need this to ignore date filters that are applied with a slicer if possible.

``````Measure =
var LD = EOMONTH(ENDOFMONTH(LASTDATE(Runtime[Date (bins)])),-1)
return
CALCULATE(DIVIDE(SUM(Costs[Maint Cost]),SUM(Runtime[RuntimeTime])),
FILTER(ALL(Dates),
Dates[Date]<=LD&&Dates[Date]>=FD
)
)``````

Here is the visual i am using this meaure for: How data is organized:  Data Pivot, Sample Calcs: 1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

## Re: Calculating and aggregating Previous Month(s) data when gaps in data are present

Create measures like so:

``Measure = IF(SUM(Costs[maint cost])=BLANK(),1,0)``
``````maxx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MAXX (
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( LASTDATE ( Dates[Date] ), Dates[YearMonth] = yearmonth )

``````
``````minx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MINX(
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( FIRSTDATE( Dates[Date] ), Dates[YearMonth] = yearmonth )
``````
``````\$/Runtime =
VAR MIN_ = [minx date]
VAR MAX_ = [maxx date]
VAR CurrentDate =
FIRSTDATE ( Dates[Date] )
VAR mindate =
CALCULATE ( FIRSTDATE ( Dates[Date] ), ALL ( Dates ) )
RETURN
IF (
DATEDIFF ( mindate, CurrentDate, MONTH ) >= 3,
CALCULATE (
DIVIDE ( SUM ( Costs[maint cost] ), SUM ( Runtime[runtime] ) ),
Dates[Date] >= MIN_
&& Dates[Date] <= MAX_
)
)
`````` BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Highlighted Super User IX

## Re: Calculating and aggregating Previous Month(s) data when gaps in data are present

@jignaski18 , for this month and last month you can use like

``````MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

previous month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

and rolling like
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))

Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))

``````

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

Highlighted Helper I

## Re: Calculating and aggregating Previous Month(s) data when gaps in data are present

Your solution does work, ONLY if there is continuous data. I am trying to address the calculation to work as below.

Your DAX results in the values highlighted in yellow. I need the results in green(right most columns). Technically the formula isnt "Previous three months". I would be more along the lines of "Previous three months that contain data" Highlighted Community Support

## Re: Calculating and aggregating Previous Month(s) data when gaps in data are present

Create measures like so:

``Measure = IF(SUM(Costs[maint cost])=BLANK(),1,0)``
``````maxx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MAXX (
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( LASTDATE ( Dates[Date] ), Dates[YearMonth] = yearmonth )

``````
``````minx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MINX(
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( FIRSTDATE( Dates[Date] ), Dates[YearMonth] = yearmonth )
``````
``````\$/Runtime =
VAR MIN_ = [minx date]
VAR MAX_ = [maxx date]
VAR CurrentDate =
FIRSTDATE ( Dates[Date] )
VAR mindate =
CALCULATE ( FIRSTDATE ( Dates[Date] ), ALL ( Dates ) )
RETURN
IF (
DATEDIFF ( mindate, CurrentDate, MONTH ) >= 3,
CALCULATE (
DIVIDE ( SUM ( Costs[maint cost] ), SUM ( Runtime[runtime] ) ),
Dates[Date] >= MIN_
&& Dates[Date] <= MAX_
)
)
`````` BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted Helper I

## Re: Calculating and aggregating Previous Month(s) data when gaps in data are present

This does work in table format when you have the YearMonth in the rows. When I try to use it in a bar chart it wouldnt return the restult. I did get it figured out though. The fix was in the  curent date variable in the \$/runtime formula

``````\$/Runtime =
VAR MIN_ = [minx date]
VAR MAX_ = [maxx date]
VAR CurrentDate =
CALCULATE(
FIRSTDATE(
Dates[Date]),
FILTER(all(Dates),Dates[month index]=0)
VAR mindate =
CALCULATE ( FIRSTDATE ( Dates[Date] ), ALL ( Dates ) )
RETURN
IF (
DATEDIFF ( mindate, CurrentDate, MONTH ) >= 3,
CALCULATE (
DIVIDE ( SUM ( Costs[maint cost] ), SUM ( Runtime[runtime] ) ),
Dates[Date] >= MIN_
&& Dates[Date] <= MAX_
)
)``````

Previous visual (incorrect forumulas): Your Formulas (the numbers are right in table):  Announcements #### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference! #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. #### Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving. Top Solution Authors
Top Kudoed Authors
Users online (1,006)