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)
var FD = DATEADD(LASTDATE(STARTOFMONTH(PREVIOUSMONTH(Runtime[Date (bins)]))),-2,MONTH)
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 IV

## 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...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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.

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

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors