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_ = DATEVALU...
  • 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!
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!