Using measures as filters on visuals

[Anonymous]

Dear community,

I'm creating a report where a user can filter the data by year and month. Within this report, I would like to include a visual that displays trends, including data from the month/year selected and the 12 months before that. I have created measures that calculate the first and last date of the trend figure;

Fdate = EDATE(date(selectedvalue('Date'[DateYear],0),selectedvalue('Date'[DateMonth],0),1),-12) // First date

Ldate = date(selectedvalue('Date'[DateYear],0),selectedvalue('Date'[DateMonth],0)+1,1)-1 // Last date

However, I don't seem to be able to use these measures as a filter in visuals.

Does anyone have a solution to this? Or any suggestions how I can create a L13M trend visual with the period depening on filtered dates?

Best regards,

Eva

[Whitewater100]

Hello:

Some good news and some bad news. You can't use measures as slicers/filters.

But you can create a calculation for say sales, e.g.

Sales = SUM(SalesTable[Revenue])

if you have data for 13 months or more, to get your trend you can use relative Date Slicer use Date field from your Date Table. Here's what is looks like and you can apply it to whichever visuals you like. Have a Date Table connected to your fact table makes life so much easier and yo can use all the built in time intelligence measures Power BI offers.

To Make a Date Table (Basic). Let's say for 2021 thru 2022

Go to Modeling.

Choose New Table. We can name it Dates.

Dates = CALENDAR(DATE(2021,1,1), DATE(2022,12,31))

You can add columns for Year & Month and Year-Month , etc

YEAR = YEAR(Dates[Date])

Month No. = MONTH(Dates[Date])

Year & Month No. = FORMAT(Dates[Date], "YYYYMM")

Then under Table tools at the top slect Mark as Date Table. Choose Date field and click OK.

Now you can connect this Date Table to a table of facts that usually have a date and some tye of transaction that goes with it, like sales, a visit, any factual type of data.

I could go on but want to see if this is helpful first. See relative slicer below using Date field from new Date Table. You can set it to 1st of the month, etc. Lot's of options.

[tamerj1]

Hi @Anonymous 
In general you can follow this rule

New Measure =
VAR Fdate =
    EDATE (
        DATE ( SELECTEDVALUE ( 'Date'[DateYear], 0 ), SELECTEDVALUE ( 'Date'[DateMonth], 0 ), 1 ),
        -12
    )
VAR Ldate =
    DATE ( SELECTEDVALUE ( 'Date'[DateYear], 0 ), SELECTEDVALUE ( 'Date'[DateMonth], 0 ) + 1, 1 ) - 1
VAR Result =
    CALCULATE (
        [Old Measure],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] >= Fdate,
        'Date'[Date] <= Ldate
    )
RETURN
    Result

[amitchandak]

@Anonymous , if you need only rolling 13 month data

Rolling 13 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-13,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-13,MONTH))

But if you select 1 month and want to have a trend for 13 months then you need an independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Anonymous]

Hi Amit,

Thank you so much for the reply - I have tried it and watched your video and is very informative, and the solution works. 🙂

I however still have a problem, the L13M trend visual is part of a report where we're going deep dive into one month. I therefore want the user to select a month in a Slicer, see some data regarding only that month, and then see the L13M trend visual as well (with the last month being the selected month). Is there any way to do this, without having the user select the month & year in a slicer for the monthly overview, and having to select it again in a seperate slicer for the L13M visual? Can I do this with a single slicer?

Thanks again for your help!

Best,
Eva

[amitchandak]

@Anonymous , if you select 1 month and try get more than one month then you have to use independent date table