topic Re: YTD Function in DAX Commands and Tips

YTD Function

PowerDev1977 — Sun, 16 Feb 2025 04:58:09 GMT

Hello Friends,

New to PowerBI.

Currently i want to compute the YTD volume (example July 2024 - Feb 2025)

I already have a DAX function for the volume

SUM OF KG =

VAR previousDay_ = DATEVALUE([PreviousWorkingDay])

RETURN

SUMX(

FILTER(

ItemMaster,

DATEVALUE(ItemMaster[Physical date]) <= previousDay_

((ItemMaster[ Quantity ] * -1)* RELATED('Item'[ MAP VOL ])

))

Now i need to compute the YTD

I wanted to re-use above function in a YTD function however it didnt work and resorted to below

FYTD_SumOfKG =

VAR PreviousWorkingDay_ = DATEVALUE([PreviousWorkingDay])

VAR FiscalYearStartDate_ = DATEVALUE([FiscalYearStartDate])

RETURN

CALCULATE

(

SUMX(

FILTER(

ItemMaster,

DATEVALUE(ItemMaster[Physical date]) >= FiscalYearStartDate_ &&DATEVALUE(ItemMaster[Physical date]) <= PreviousWorkingDay_

((ItemMaster[ Quantity ] * -1) * RELATED('Item'[ MAP VOL ]))

REMOVEFILTERS('calendar'[Year],'calendar'[Month Name])

)

My point is i want to really use YTD cause it seems im not optimising my code. Anyone can help me re-write the code using YTD.

Rgds

Re: YTD Function

tharunkumarRTK — Sun, 16 Feb 2025 06:56:22 GMT

PowerDev1977

Its difficult to suggest any change without knowing the data model and sample data. But I will suggest

First rewrite the 'Sum of KG measure'

SUM OF KG = SUMX( ItemMaster, ( (ItemMaster[ Quantity ] * -1)* RELATED('Item'[ MAP VOL ] ) )

You can reuse it in the YTD measure like this

FYTD_SumOfKG = VAR PreviousWorkingDay_ = DATEVALUE([PreviousWorkingDay]) VAR FiscalYearStartDate_ = DATEVALUE([FiscalYearStartDate]) RETURN CALCULATE ( [Sum of KG], FILTER( ItemMaster, DATEVALUE(ItemMaster[Physical date]) >= FiscalYearStartDate_ &&DATEVALUE(ItemMaster[Physical date]) <= PreviousWorkingDay_) , REMOVEFILTERS('calendar'[Year],'calendar'[Month Name]) )

Intead of the above pattern I woud suggest you to use time intelligence function like DatesYTD and pass the second parameter to last date of the financial year.

FYTD_SumOfKG = CALCULATE ( [Sum of KG], DATESYTD(Calendar[Date], "6-30") )

For further assitance please share the pbix file

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Re: YTD Function

PowerDev1977 — Sun, 16 Feb 2025 14:56:34 GMT

Thanks Tharun,

Actually this is what i did and it worked.

I have another issue which i would like to have your guidance.

I have my budgeted sales table which is linked to my calendar table. However , i want the match to be done on the month rather than date. i have inserted firstdate of the month for the Budgeted sales month so it is normal calendar will not find all the the dates, i want the matching to be done on the month rather

example

Budgeted Sales table contains

Date Month Sales

01/01/2025 1 2000
01/02/2025 2 3000

Now calendar contains

Date workingDayRank NumberOfDays month

01/01/2025 24 1 -- working day rank empty since non-working day

02/01/2025 24 1 -- working day rank empty since non-working day

03/01/2025 1 24 1

04/01/2025 2 24 1

I have to perform a calculation for each row (and i need to ignore the relationship on date)

sales prorata = (sales * workingDayRank )/NumberOfDays

Used below, but still it ignore January cause it seems it is still matching on the date.

Budgeted Sakes Prorata (Rank) =

CALCULATE

(

SUMX(

BudgetSalesVol,

DIVIDE

(

BudgetSalesVol[Volume] * IF(ISBLANK(RELATED('calendar'[WorkingDayRank_Month])),0,RELATED('calendar'[WorkingDayRank_Month])),

RELATED('calendar'[NumberOfWorkingDaysInMonth])

)

REMOVEFILTERS('calendar'[Date]),

REMOVEFILTERS(BudgetSalesVol[Date]),

USERELATIONSHIP('calendar'[MonthNumber] ,BudgetSalesVol[Month])

)

Re: YTD Function

tharunkumarRTK — Sun, 16 Feb 2025 15:02:55 GMT

PowerDev1977

Glad to know that you are able to implement the approach suggested.

I would request you to create a new thread for this new question

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Re: YTD Function

Anonymous — Tue, 18 Feb 2025 05:29:54 GMT

Hi PowerDev1977 ，

I am happy to learn that you found the cause of the problem and fixed it.

Please accept tharunkumarRTK 's reply as a solution, it will make it easier for other users who may be experiencing the same problem to find a solution.

Thanks for your understanding.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.