Burndown

[cosmasadesegha]

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"))

[parry2k]

@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-ca...

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]

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 · Accepted solution]

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.