YTD Function

[PowerDev1977]

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

[Anonymous]

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.

[tharunkumarRTK · Accepted solution]

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

[PowerDev1977]

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

)

[tharunkumarRTK]

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