Forum Discussion

PowerDev1977's avatar
PowerDev1977
Regular Visitor
1 year ago
Solved

YTD Function

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
  • tharunkumarRTK's avatar
    tharunkumarRTK
    1 year ago

    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!

4 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    1 year ago

    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's avatar
      PowerDev1977
      Regular Visitor
      1 year ago

      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's avatar
        tharunkumarRTK
        Super User
        1 year ago

        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!
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.