Forum Discussion

jignaski18's avatar
jignaski18
Icon for Helper II rankHelper II
6 years ago
Solved

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 th...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
jignaski18
Icon for Helper II rankHelper II
6 years ago

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