Partially ignore Date slicer

[Robert1981]

I have a dashboard with different KPI's. On top of the page is a Date year/month slicer. 
On the same page I want a graph with the KPI value for the past 12 months. The 12 months should end with the selected Slicer year/month and count 12 months back.

When I leave the dependency between the slicer and the graph, then I only get 1 period. When I disconnect the dependency, I get all months in my dataset.

Here is the formula I set up:

DPO_M_o_M =
VAR Current_Date=max(dimDate[Date])
Var Max_Date=SELECTEDVALUE(dimDate[Date])
Var Bottom_Date=eomonth(Max_date,-18)
VAR AP=calculate(sum(DPO[MX_Act_AP]))
VAR COGS=calculate(sum(DPO[MX_Act_CostofRev3Mon]))
VAR AP_PM=calculate(sum(DPO[MX_Act_AP]),All(dimDate),DPO[MX_Date]=eomonth(Current_Date,-2)+1)
VAR COGS_PM=calculate(sum(DPO[MX_Act_CostofRev3Mon]),All(dimDate),DPO[MX_Date]=eomonth(Current_Date,-2)+1)
VAR DPO_Delta=divide(AP,COGS,0)*90-divide(AP_PM,COGS_PM,0)*90
VAR Result= If(Current_Date>=Bottom_Date && Current_Date<=Max_Date,DPO_Delta)

return

Result

I was expecting the graph only to be between bottom_date and Max_Date.
How can I make this work?

[Anonymous · Accepted solution]

Hi @Robert1981 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a date table.

Then create a measure.

Measure =
VAR _1 =
    SELECTEDVALUE ( dimdate[Date] )
VAR _12 =
    EDATE ( _1, -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= _12
                && 'Table'[date] <= _1
                && 'Table'[month] = SELECTEDVALUE ( 'Table'[month] )
                && 'Table'[year] = SELECTEDVALUE ( 'Table'[year] )
        )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Daniel29195]

hello @Robert1981

can you please share a presentation of the visually ou want to achieve with some dummy data. 

best regards,