Forum Discussion

IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

DAX Help: Running YTD Total by Day

Looking for help on this Dax Measure I wrote.  Below is the Measure that Needs Adjusting Note - Company Fiscal Year is May - April PY Net Sales = SWITCH([Selected Period], "MTD", CALCULATE(SUM(S...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution Greg_Deckler  and 123abc  offered and i want to offer some more information for user to refer to.

    hello IamTDR , you can create the following measure

    MEASURE =
VAR a =
    CALCULATE (
        MIN ( Dim_Date[Date] ),
        ALL ( Dim_Date ),
        Dim_Date[Fiscal Year] = 2024
    )
RETURN
    IF (
        SUM ( 'Table'[sales_net] ) <> BLANK ()
            && MAX ( Dim_Date[Fiscal Year] ) = 2024,
        CALCULATE (
            SUM ( 'Table'[sales_net] ),
            FILTER ( Dim_Date, [Date] >= a && Dim_Date[Date] <= EDATE ( TODAY (), -12 ) )
        )
    )

    Ouptut

    Best Regards!

    Yolo Zhu

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

     

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

The key here is to use MIN or MAX on the selected date from your slicer and compare it to TODAY() to ensure the YTD calculation is limited to the current date if it's within the selected month.

Plz try this measure:

PY Net Sales =
SWITCH(
[Selected Period],
"MTD",
CALCULATE(
SUM(Source_TerritorySales_FY25[sale_net]),
Dim_Date[Fiscal Year] = 2024
),
"YTD",
CALCULATE(
SUM(Source_TerritorySales_FY25[sale_net]),
Dim_Date[Fiscal Year] = 2024,
DATESBETWEEN(
Dim_Date[Date],
STARTOFYEAR(Dim_Date[Date], "4/30"),
MIN(TODAY(), MAX(Dim_Date[Date]))
)
),
BLANK()
)

 

Example:

  • If the user selects "YTD" and the month "August", and today is "8/8/2023", the measure will sum up the sales from "5/1/2023" to "8/8/2023".
  • If the user selects a future month, e.g., "September," the measure will sum from "5/1/2023" to "8/8/2023" (today) because TODAY() is earlier than the end of September.
  • IamTDR's avatar
    IamTDR
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Thanks for the reply
    I am not getting the desired outcome with the proposed measure.
    Adding a screenshot of some sample data.
    So if my End-User is Selecting the  Month 'AUG' and they want to switch between MTD and YTD

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      Thanks for the solution Greg_Deckler  and 123abc  offered and i want to offer some more information for user to refer to.

      hello IamTDR , you can create the following measure

      MEASURE =
VAR a =
    CALCULATE (
        MIN ( Dim_Date[Date] ),
        ALL ( Dim_Date ),
        Dim_Date[Fiscal Year] = 2024
    )
RETURN
    IF (
        SUM ( 'Table'[sales_net] ) <> BLANK ()
            && MAX ( Dim_Date[Fiscal Year] ) = 2024,
        CALCULATE (
            SUM ( 'Table'[sales_net] ),
            FILTER ( Dim_Date, [Date] >= a && Dim_Date[Date] <= EDATE ( TODAY (), -12 ) )
        )
    )

      Ouptut

      Best Regards!

      Yolo Zhu

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

       

      • IamTDR's avatar
        IamTDR
        Icon for Responsive Resident rankResponsive Resident
        2 years ago

        Thanks all for the much needed help on this!