Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jignaski18
Helper II
Helper II

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:
visual.JPG

 

 

How data is organized:

runtimes.JPGcosts.JPG

 

 

 

 

 

 

 

 

 

 

 

Data Pivot, Sample Calcs:

pivots.JPG

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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

run.PNG

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.

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

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

run.PNG

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.

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

 

visual.JPG

 

 

 

 

 

 

Your Formulas (the numbers are right in table):

 

visual2.JPG

 

 

 

 

 

 

 

 

 

tempsnip.png

amitchandak
Super User
Super User

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

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"

Capture.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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