Filter a measure by selected slicer date

[AVGUser_PBI]

HI everyone,

I am creating a pivot table that combines two cost data. 

1. projected cost: consists of all current and future month cost. captured every month.

projected cost
item	projected cost	effective date	capture date
a	50	11/1/2024	11/1/2024
a	50	12/1/2024	11/1/2024
a	100	1/1/2025	11/1/2024
a	50	12/1/2024	12/1/2024
a	50	1/1/2025	12/1/2024
a	100	1/1/2025	1/1/2025

2. real cost: continous table consists of real cost occured for past months. Doesn't have capture date column. so I duplicated effective date column and named it capturedate column

real cost
item	real cost	effective date	capture date
a	60	11/1/2024	11/1/2024
a	40	12/1/2024	12/1/2024

I am trying to create a pivot table as shown below. i have a date slicer for capture date. If I select capture date to be 12-2024, then table should show me all real cost before 12-2024 and projected cost on and after 2024 monthly:

result pivot table

my data model is like:

I can get the projected cost correctly. but I am unable to get real cost.

my current real cost formula:

CALCULATE(

    ‘real cost’[real cost],

    FILTER(

        ALL('calendar_capturedate'[Date].[Date]),

        'calendar_capturedate'[Date].[Date] < SELECTEDVALUE('calendar_capturedate'[Date].[Date])

    )

)

can you help me figure out real cost in this scenario?

thank you, 

[Anonymous · Accepted solution]

Hi @AVGUser_PBI ,

Thanks for the reply from rohit1991 / shafiz_p .

Use the following syntax to create a date table that does not require a relationship to be established, this date table projected cost table and real cost table can be used.

Date = 
ADDCOLUMNS (
    CALENDAR (
        MIN (
            MIN ( 'projected cost'[capture date] ),
            MIN ( 'real cost'[capture date] )
        ),
        MAX (
            MAX ( 'projected cost'[capture date] ),
            MAX ( 'real cost'[capture date] )
        )
    ),
    "Year_Month", FORMAT ( [Date], "YYYY-MM" )
)

Create two measures to calculate projected cost and real cost.

Projected Cost =
VAR _slicer =
    SELECTEDVALUE ( 'projected cost'[capture date] )
VAR _currentdate =
    MAX ( 'Date'[Date] )
VAR _ProjectedCost =
    CALCULATE (
        SUM ( 'projected cost'[projected cost] ),
        FILTER (
            ALLSELECTED ( 'projected cost' ),
            YEAR ( 'projected cost'[effective date] ) = YEAR ( _currentdate )
                && MONTH ( 'projected cost'[effective date] ) = MONTH ( _currentdate )
        )
    )
RETURN
    IF (
        DATE ( YEAR ( _currentdate ), MONTH ( _currentdate ), 1 )
            >= DATE ( YEAR ( _slicer ), MONTH ( _slicer ), 1 ),
        _ProjectedCost
    )

Real Cost =
VAR _slicer =
    SELECTEDVALUE ( 'projected cost'[capture date] )
VAR _currentdate =
    MAX ( 'Date'[Date] )
VAR _RealCost =
    CALCULATE (
        SUM ( 'real cost'[real cost] ),
        FILTER (
            ALLSELECTED ( 'real cost' ),
            YEAR ( 'real cost'[effective date] ) = YEAR ( _currentdate )
                && MONTH ( 'real cost'[effective date] ) = MONTH ( _currentdate )
        )
    )
RETURN
    IF (
        DATE ( YEAR ( _currentdate ), MONTH ( _currentdate ), 1 )
            < DATE ( YEAR ( _slicer ), MONTH ( _slicer ), 1 ),
        _RealCost
    )

Using a matrix visual object, put the Year-month field of the date table into columns, put the two measures created into values, and convert the values into rows.

The final visual is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
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!

[AVGUser_PBI]

fantastic! this worked

[rohit1991]

Hi , @AVGUser_PBI 

Here’s a straightforward way to do it:

1. Check your data model: Make sure your date table (the one used for your slicer) is related to the date column in your real cost table usually calendar_capturedate[Date] to real cost[capturedate].
   
   

2. Create your measure like this: If you want to sum all real cost values up to and including the selected date from your slicer, use:Real Cost Filtered =
   CALCULATE(
   SUM('real cost'[real cost]),
   FILTER(
   ALL('real cost'),
   'real cost'[capturedate] <= SELECTEDVALUE('calendar_capturedate'[Date])
   )
   )

3. If you don’t have a relationship: You can use TREATAS to “push” the slicer date into your real cost table:Real Cost Filtered =
   CALCULATE(
   SUM('real cost'[real cost]),
   TREATAS(
   VALUES('calendar_capturedate'[Date]),
   'real cost'[capturedate]
   )
   )
   ​

[AVGUser_PBI]

hi, thank you for the response. unfortunately its not working. I suspect that there is an issue with my data model relationship. 

[shafiz_p]

Hi @AVGUser_PBI  You are referencing a naked column inside calculate function. 

Try wraping 'real cost'[real cost] within summarization fuction. In your case, use SUM('real cost'[real cost]).

Hope this helps.

[AVGUser_PBI]

hi, thank you for the response. unfortunately its not working. I suspect that there is an issue with my data model relationship.