cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:
visual.JPG

 

 

How data is organized:

runtimes.JPGcosts.JPG

 

 

 

 

 

 

 

 

 

 

 

Data Pivot, Sample Calcs:

pivots.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

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
Highlighted
Super User IV
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://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.



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
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"

Capture.JPG

Community Support
Community Support

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

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

Highlighted
Helper I
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):

 

visual.JPG

 

 

 

 

 

 

Your Formulas (the numbers are right in table):

 

visual2.JPG

 

 

 

 

 

 

 

 

 

tempsnip.png

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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