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

[jignaski18]

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:

[Icey · Accepted solution]

Hi @jignaski18 ,

Please check:

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.

[jignaski18]

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):

[amitchandak]

@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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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...


Appreciate your Kudos.

[jignaski18]

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"