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

amitchandak's avatar
amitchandak
Super User
4 years ago

JakeJack , Assuming Dates is a date table try measures like 

 

Try measures like

 

YTD = CALCULATE([Daily Prod Value],DATESYTD('Dates'[CurDate],"6/30"))


Last YTD = CALCULATE([Daily Prod Value],DATESYTD(dateadd('Dates'[CurDate],-1,Year),"6/30"))

 

Cumm = CALCULATE([Daily Prod Value],filter(allselected(dates),dates[CurDate] <=max(dates[CurDate])))

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

 

 

  • JakeJack's avatar
    JakeJack
    Frequent Visitor
    4 years ago

    Hi

     

    Thanks for getting back.

     

    Your suggestions didn't work.

     

    I have maked the Dates table as a date table.

     

    The earliest date is 1st July 2016 and last date is 31st Dec 2025.

     

    The Daily Prod table has data from  12th Sep 2017.

     

    Any idea why my running total measure is failing?  The daily sales one form start of fiscal year is working.

     

    Thanks again

     

    Jake