Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Backlog Revenue for each weeknumber

Hi all,

 

I have read through a lot of post on this forum but I can't seem to find the right solution for my problem. Hopefully someone can help me to create a historical bar chart. In the screenshots below I explain what my input table is and what output I expect in my bar chart.

 

I have a table like this (small part as example), where validfrom and validtill indicate when a record was active:

 

Next to this I have my general dates table, with for instance YearWeek, YearMonth etc.

As my output I would like a bar chart like this below where for each weeknumber the SUM of revenues active in this week is presented. I want to use the last day of the week as measure point, and thus view a record in a certain week if the last day of the week is between ValidFrom and ValidTill:

 

Desired output for January 2021:

 

 

Thanks for the help in advance! ðŸ˜€

 

 

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ï¼Œ

    Test like the below steps:

    base table you provided:

    Step1,create a date table:

    Table2 = CALENDAR("2021,1,1","2021,12,31")
    week = WEEKNUM(Table2[Date])

    Step 3, create week table:

    Table3 = DISTINCT(Table2[week])
    maxdate = CALCULATE(MAX(Table2[Date]),FILTER(Table2,Table2[week]=Table3[week]))
    mindate = CALCULATE(MIN(Table2[Date]),FILTER(Table2,Table2[week]=Table3[week]))

    Then use the below dax to  create a new column:

    Column = CALCULATE(SUM('Table'[AccountEUR]),FILTER('Table',Table3[maxdate]>='Table'[validfrom ]&&Table3[maxdate]<='Table'[validtill]))

    Final output:

    You could download my pbix file if you need!

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

3 Replies