Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Display different calculated measure values for different end of the month dates.

Hi All,

 

Can you please help me with the below scenario.

 

For current month end date: (29 Feb 2025)

(sum of sales where Date1 <= today()) - (sum of the sales where Date2 <= end of month today) + (sum of sales where Date2 >= end of month of today() && Date1 >= today() && Date1 <= end of month today)

 

For next month end date: (31 Mar 2025)

(sum of sales where Date1 <= today()) - (sum of the sales where Date2 <= end of month March) + (sum of sales where Date2 >= end of month of March && Date1 >= today() && Date1 <= end of month March)

 

and so on ... for every month end different calculation. It should display these values against each month end in a list or line graph.

 

Thanks in advance, 

Shiva.

Need Help

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    Anonymous Create a Date Table (if you don't already have one):

    DateTable = CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31))

     

    Create Measures:

    DAX
    SumOfSales_Date1 = CALCULATE(SUM(Sales[Amount]), Sales[Date1] <= TODAY())

    SumOfSales_Date2_EndOfMonth =
    VAR EndOfMonthDate = EOMONTH(TODAY(), 0)
    RETURN
    CALCULATE(SUM(Sales[Amount]), Sales[Date2] <= EndOfMonthDate)

    SumOfSales_Date2_Conditions =
    VAR EndOfMonthDate = EOMONTH(TODAY(), 0)
    RETURN
    CALCULATE(
    SUM(Sales[Amount]),
    Sales[Date2] >= EndOfMonthDate &&
    Sales[Date1] >= TODAY() &&
    Sales[Date1] <= EndOfMonthDate
    )

     

    DAX
    DynamicMeasure =
    VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)
    VAR NextMonthEnd = EOMONTH(TODAY(), 1)
    RETURN
    SWITCH(
    TRUE(),
    SELECTEDVALUE(DateTable[Date]) = CurrentMonthEnd,
    [SumOfSales_Date1] - [SumOfSales_Date2_EndOfMonth] + [SumOfSales_Date2_Conditions],
    SELECTEDVALUE(DateTable[Date]) = NextMonthEnd,
    [SumOfSales_Date1] - CALCULATE(SUM(Sales[Amount]), Sales[Date2] <= NextMonthEnd) +
    CALCULATE(
    SUM(Sales[Amount]),
    Sales[Date2] >= NextMonthEnd &&
    Sales[Date1] >= TODAY() &&
    Sales[Date1] <= NextMonthEnd
    ),
    -- Add more conditions for other months if needed
    BLANK()
    )

     

    Use a line chart or a table to display the DynamicMeasure against the DateTable[Date].

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Bhanu, 

      Thank you for your reply. But still I'm not getting the required output.

      For eg, I have taken your formula and created below measure for just one simple calculation. 

       

      DynamicMeasure =
      VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)
      VAR NextMonthEnd = EOMONTH(TODAY(), 1)
      RETURN
      SWITCH(
      TRUE(),
      SELECTEDVALUE(Backlog_2[OPD]) = CurrentMonthEnd,
      CALCULATE(SUM(Backlog_2[Sales]), Backlog_2[Est. Ship Date] <= EOMONTH(TODAY(),0)
      ))

      Result:

      Here, the DynamicMeasure is giving 713983.87 as it is filtering for the month end 28 feb. But I need the entire calculation "[SumOfSales_Date2_EndOfMonth]" which is 13276175.28 to be stored in 28th feb 2025. 

      Can you please help me.

       

      Thanks,

      Shiva.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous , hello bhanu_gautam, thank you for your prompt reply!

         

        From your description, it seems that the original post is different from the latest post. Could you please help verify the actual requirements?