Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cosmasadesegha
Frequent Visitor

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.jpg

 

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

 

1 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.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.