Forum Discussion

JakeJack's avatar
JakeJack
Frequent Visitor
4 years ago
Solved

Running Total Help

Hi All   Still trying to get to grips with some basic aspects of PowerBI.   I've a simple data model in play:  a date table and a daily production table with relationship between Dates[CurDate] a...
Need Help
  • JakeJack's avatar
    JakeJack
    4 years ago

    Hi All

     

    I believe I have found a nice solution to my query.

     

    I came across this great article https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/ 

     

    So I followed the 2 methods described.

     

    I created a calculated column in the Dates table:

     

    DatesWithProdVals = 
    'Dates'[CurDate] <= MAX ( DailyProd[pdate] )

     

    This column had a value of True up to and including the maximum production date.

     

    I then created the following measure:

     

    Sales YTD v1 = 
CALCULATE (
    [CY Daily Prod Value],
    CALCULATETABLE (
        DATESYTD ( Dates[CurDate],"30/06" ),
        Dates[DatesWithProdVals] = TRUE
    )
)

     

    This measure gave me exactly what i wished.

     

    I also followed the described procedure without creating a calculated column:

     

    Sales YTD v2 = 
VAR LastDayAvailable =
    CALCULATE (
        MAX ( DailyProd[pdate] ),
        ALL ( DailyProd )
    )
VAR FirstDayInSelection =
    MIN ( 'Dates'[CurDate] )
VAR ShowData =
    (FirstDayInSelection <= LastDayAvailable)
RETURN 
IF (
        ShowData,
        CALCULATE (
            [CY Daily Prod Value],
            DATESYTD ( 'Dates'[CurDate],"30/06" )
        )
    )

     

    This again produced the running ytd values for only July - December.  However this measure does not display a total value (see table below).  I don't know why.  

     

    YTD Table

    Thanks everyone for you help.

     

    Until the next time ğŸ˜‰

     

    Jake

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

Hi JakeJack ,

According to your description, here's my solution.

1.Create another month column in the Dates table.

Month_num = MONTH('Dates'[Date])

2.Create the RT measure.

RT =
SUMX (
    FILTER ( ALL ( 'Dates' ), 'Dates'[Month_num] <= MAX ( 'Dates'[Month_num] ) ),
    [CY Daily Prod Value]
)

 Get the expected result.

Best Regards,
Community Support Team _ kalyj

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.