Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YTD Cumulative Numbers by week

Experts,

 

I have four year worth of data in my source table. They are transactions by date. I am trying to retrieve YTD numbers by week. I used following formula. Without any filters, Power BI is retrieving current year or 2020 numbers. When I do the same for 2019, with or without filter ( 2019), it is showing blank. I do have a calendar table set up. I have the canvas, source data table, and calendar table attached. Please kindly advise. I think that my source data might need to be massaged. 

 

Tonnes 2020 Cumulative = TOTALYTD(sum('Scale Data 2019 2020'[Tonnes]),'Calendar Table'[Date])
 
Thank you!!!
 
Jen

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    Time intelligence function does not agility enough to work with all scenarios. (it also not support to do customize internal calculations or nested with multiple time intelligence functions)
    BTW, it also requires completed table records and a common structure date calendar to do rolling calculations.

    For your scenario, I'd like to suggest you try to manually use year and weeknum function to manually defined the filter range for calculation, it should dynamic interaction with filters.

    Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( Table[Date] ) = YEAR ( currDate )
                && WEEKNUM ( Table[Date], 1 ) <= WEEKNUM ( currDate, 1 )
        )
    )

    Reference link:

    Time Intelligence "The Hard Way" (TITHW)  
    Regards,

    Xiaoxin Sheng

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes,

       

      But then, why it's showing only current year info. I have the numbers in the source data from 2017. It's only showing 2020. 

       

      And if put a filter for 2019 on, it's showing blank.

       

      Jen

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , Formula is fine. Unless there is some other filter or the date join have some issue like joined to some other date. Or date has a timestamp.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    Time intelligence function does not agility enough to work with all scenarios. (it also not support to do customize internal calculations or nested with multiple time intelligence functions)
    BTW, it also requires completed table records and a common structure date calendar to do rolling calculations.

    For your scenario, I'd like to suggest you try to manually use year and weeknum function to manually defined the filter range for calculation, it should dynamic interaction with filters.

    Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( Table[Date] ) = YEAR ( currDate )
                && WEEKNUM ( Table[Date], 1 ) <= WEEKNUM ( currDate, 1 )
        )
    )

    Reference link:

    Time Intelligence "The Hard Way" (TITHW)  
    Regards,

    Xiaoxin Sheng