How to calculate previous month using a slicer with text dates

[lava_]

Context: I have a date column and I had to create a calculated column based off of that which 1) converts those dates into text and 2) it does that so I could have the latest date be called "Current". I use this calculated column in a slicer so the latest date (aka "Current") is always selected. 

Problem: My calculation that finds the sum of market value for the previous month based on what's selected in the slicer, are no longer working. They just show up as blank.  

The Data Table: For this calculation, I'm just using one data table. That includes the AsOfDate, ID, MarketValue columns as well as my calculated column AsOfDate_SlicerValues.

NOTE - My original date column is called "AsOfDate", which is what is used in the below calculation - not the calculated column for the slicer. 

Here is the formula:

Date Selected_Mrkt Value Sum_1 Mo Prior =

VAR MaxDate = CALCULATE(MAX('AUM Data'[AsOfDate]), ALL('AUM Data'))  //  this works fine and returns the correct value

VAR DateSelected = SELECTEDVALUE('AUM Data'[AsOfDate], MaxDate)  // this also works fine and returns the correct value

VAR PrevMonth = EOMONTH(DateSelected, -1)  // this also works fine and returns the correct value

VAR Result =

            CALCULATE(

                SUM('AUM Data'[MarketValue]),

                'AUM Data'[AsOfDate] = PrevMonth  // THIS is the part that is NOT working

            )

RETURN

Result

[lava_]

Here is the solution in case anyone comes acorss this issue. I needed to create a separate table with the AsOfDate column and then the calculated slicer values column in order for this to work.

CALCULATE(

                                SUM('AUM Data'[MarketValue]),

                                    CROSSFILTER('AUM Data'[AsOfDate], 'AsOfDate Slicer Table'[AsOfDate], None),

                             'AUM Data'[AsOfDate] =

                                CALCULATETABLE(

                                    VALUES('AsOfDate Slicer Table'[AsOfDate]),

                                        FILTER(

                                            ALL('AsOfDate Slicer Table'),

                                            'AsOfDate Slicer Table'[AsOfDate] = EOMONTH(SELECTEDVALUE('AUM Data'[AsOfDate]), -1)

                                        )

                                    )

                                )

[Anonymous]

Hi @lava_ ，

VAR PrevMonth = EOMONTH(DateSelected, -1)

If ‘AUM Data’[AsOfDate] contains dates that are not always the end of the month, this comparison could fail. Please check if this is the cause.

Date Selected_Mrkt Value Sum_1 Mo Prior =
VAR MaxDate =
    CALCULATE ( MAX ( 'AUM Data'[AsOfDate] ), ALL ( 'AUM Data' ) ) //  this works fine and returns the correct value
VAR DateSelected =
    SELECTEDVALUE ( 'AUM Data'[AsOfDate], MaxDate ) // this also works fine and returns the correct value
VAR PrevMonth =
    EOMONTH ( DateSelected, -1 ) // this also works fine and returns the correct value
VAR PrevMonthExt =
    CALCULATE (
        MAX ( 'AUM Data'[AsOfDate] ),
        FILTER ( ALL ( 'AUM Data' ), 'AUM Data'[AsOfDate] <= PrevMonth )
    )
VAR Result =
    CALCULATE (
        SUM ( 'AUM Data'[MarketValue] ),
        'AUM Data'[AsOfDate] = PrevMonthExt
    )
RETURN
    Result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Sahir_Maharaj]

Hello @lava_,

Can you please try this:

Date Selected_Mrkt Value Sum_1 Mo Prior =
VAR MaxDate = CALCULATE(MAX('AUM Data'[AsOfDate]), ALL('AUM Data'))
VAR DateSelected = SELECTEDVALUE('AUM Data'[AsOfDate], MaxDate)
VAR PrevMonth = EOMONTH(DateSelected, -1)

VAR Result =
    CALCULATE(
        SUM('AUM Data'[MarketValue]),
        FILTER(
            'AUM Data',
            'AUM Data'[AsOfDate] = PrevMonth
                || 'AUM Data'[AsOfDate] = "Current"  // Add this condition
        )
    )

RETURN
Result

Should you require any further assistance, please do not hesitate to reach out to me.

[lava_]

Thank you for your response, @Sahir_Maharaj. That didn't work unfortunately. My AsOfDate column does not include the "Current" value. The AsOfDate_SlicerValues column does, which is what I'm using in my slicer. But in my calculation that I posted, I'm still using my original AsOfDate column since that is properly formatted as dates and I can do the date calculations with that. Should I be using the AsOfDate_SlicerValues column in the formula instead? I just can't get the date calculation to work even when I convert the text to a date.