Forum Discussion

cosmasadesegha's avatar
cosmasadesegha
Frequent Visitor
6 years ago
Solved

Burndown

Hey Guys,

 

I am trying to burdown a project fund that span through two years. I was trying this Dax function that someone shared here but it was  not working as expected.......DATESYTD('Date'[DateKey], "31/12"

 The burdown has to start again when it gets to another year. Please see the Matrix table below with the code. Thanks for your support. 

 

 
 

 

Burndown =

var wbs = SELECTEDVALUE(JobTask[Job_WBS_Code])
var WBSFundingAmount= CALCULATE(SUMX(RELATEDTABLE(JobTaskCard),
CALCULATE(SUM(JobTaskCard[Funded_Amount]), FILTER(JobTask, JobTask[Job_WBS_Code]=wbs))))
VAR StartYear= CALCULATE(max(JobTaskCard[Starting_Date]), JobTask[Job_WBS_Code]=wbs)
VAR EndYear= CALCULATE(max(JobTaskCard[Ending_Date]), JobTask[Job_WBS_Code]=wbs)

return
//wbs
WBSFundingAmount - CALCULATE( [SumTotalTest], DATESYTD('Date'[DateKey], "31/12"))

 

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi cosmasadesegha 

     

    Not sure if you have solved this or not, but have you tried to change this part of your measure

    WBSFundingAmount - CALCULATE( [SumTotalTest], DATESYTD('Date'[DateKey], "31/12"))

    to

    WBSFundingAmount
    - CALCULATE (
        [SumTotalTest],
        FILTER (
            ALL ( 'Date' ),
            'Date'[year] = MAX ( 'Date'[year] )
                && 'Date'[DateKey] <= MAX ( 'Date'[DateKey] )
        )
    )

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    cosmasadesegha As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

    https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

     

    You should use this date table for DATESYTD calculations.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • cosmasadesegha's avatar
      cosmasadesegha
      Frequent Visitor
      6 years ago

      Thanks Parry2k for your response. I actually have a date table and I used it for time intellegence calculcation. The issue is I need the right dax fuction to fix that problem...

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        Hi cosmasadesegha 

         

        Not sure if you have solved this or not, but have you tried to change this part of your measure

        WBSFundingAmount - CALCULATE( [SumTotalTest], DATESYTD('Date'[DateKey], "31/12"))

        to

        WBSFundingAmount
    - CALCULATE (
        [SumTotalTest],
        FILTER (
            ALL ( 'Date' ),
            'Date'[year] = MAX ( 'Date'[year] )
                && 'Date'[DateKey] <= MAX ( 'Date'[DateKey] )
        )
    )

         

        Cheers,
        Sturla

        If this post helps, then please consider Accepting it as the solution. Kudos are nice too.