Forum Discussion

Anonymous

Not applicable

6 years ago

YTD Cumulative for weekly figures

I am trying to create measure for the cumulative columes. Below is the DAX code I wrote. As you can see in the last colume of the visual, it is still showing by week numbers not cumulative by week...

Icon for Post Patron rank

Post Patron

6 years ago

[my first attempt to answer a query for someone else!!] 🙂

I assume you already have a calendar set up? But replace the calendar[date] in my version for your date column and it should work for you.

this is a good cumulative measure that I use a lot if you do:

Tonnes 2019 cumulative = CALCULATE('scale data 2019 2020'[tonnes],

FILTER(ALLSELECTED(calendar'[Date] ) ,

'calendar'[Date] <= MAX('00_calendar'[Date])))

Anonymous
Not applicable
6 years ago
I didn't actually. I had no idea what a Calendar is. A regular calendar? In columnar format? Add as a new colume?

Jen
- Anonymous
  Not applicable
  6 years ago
  Thanks a lot for your reply.
  - Pmorg73
    Post Patron
    6 years ago
    It would be worth your while quickly researching creating calendars (loads on youtube - check Enterprise DNA). It gives you a new table that you make a relationship to your data table with. Then you have Time inteligence (as they call it) and you can measure against a set calendar range, using days, months, years or whatever.
    
    eg
    
    create Table button and enter this:
    calendar = CALENDAR(DATE(2018,04,01),date(2021,03,31))
    
    from here create columns for months, weeks, Financial year months, month numbers. Lots and lots of options that you adjust for your requirements. I typically have a month number; financial year month number; year; quarter, Day number; Day of week number
    eg -
    month number = month('calendar'[Date])
    Day = DAY('calendar'[Date])
Anonymous
Not applicable
6 years ago
Thanks a lot for your reply. Jen