Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YTD Cumulative for weekly figures

  Tonnes Last Year  Tonnes This Year Tonnes Last Year Cumulative Tonnes This Year Cumulative Week 1 250 238 250 238 Week 2 300 526 550 764 Week 3 125 114 675 878 Week 4...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    Please create one calculated column to get the week number first, then create one measure to cumulative the weekly tonnes. The related formulas as below:

    Week = WEEKNUM('Scale date 2019 2020'[Date],2)
    Tonnes 2019 cumulative = 
VAR curweek =
    MAX ( 'Scale date 2019 2020'[Week] )
RETURN
    CALCULATE (
        SUM ( 'Scale date 2019 2020'[Tonnes] ),
        FILTER (
            ALL ( 'Scale date 2019 2020' ),
            YEAR ( 'Scale date 2019 2020'[Date] ) = 2019
                && 'Scale date 2019 2020'[Week] <= curweek
        )
    )

    Best Regards

    Rena

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , Not sure on the need to if clause,

You should try like

Tonnes 2020 cumulative = TotalYTD(sum('data table'[Tonnes]),'data table'[date])

Prefer with a date table

Tonnes 2020 cumulative = TotalYTD(sum('data table'[Tonnes]),'Date'[date])

And Create week in your date table and use from there.

 

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/