T12, T6 etc. with dynamic date filters

[Kavelar1]

Hi there!

The gist of it, I've been struggling with finding a way to have T-12 data showcase based on a year slicer and a month slicer for line/bar charts etc. What I am trying to figure out is if there is a way to have a "range date measure or related" that dynamically changes based on the year and month slicers rather than a measure that calculates "The most recent data" I have access to or have to make hundreds of measures for each calculation/data I have. With this dynamically changing date range, I can quickly edit/copy to showcase data for the last 3yrs, 6 months, or 12 months etc. based on the selected month/year slicer. 

For instance:

In a dashboard I am trying to show MTD and MTM changes, but also a Net Income Per Year chart that shows the data per the last three years. The problem comes when I select the year/month, lets say for january 2025, I don't want the user to see the data for Feb-now 2025 on that net income per year. Even more, for all the years available.

I've been looking around and have found no solution for what I am looking for.

Any info on this would be very helpful!

Thanks!

[v-pgoloju]

Hi @Kavelar1,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @amitchandak  and @Ashish_Mathur  for prompt and helpful responses.

Just following up to see if the responses provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[amitchandak]

@Kavelar1 , Based on what I got you need a date slicer and you also need a slicer with 3M, 6M, 12M slicers, based on that you should show the trend 

You can measure like this for 12 month trend 

//Date1 is independent/disconnected Date table, Date is joined with Table , slicer is on disconnected Date Table , axis is on connected date table 
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

//DateInd is joined with Inactive join, Date is joined with Table with Active join. slicer is on connected Date Table , axis is on disconnected date table 
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return
CALCULATE( sum(sales[Gross Sales]),filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ,USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))

//DateInd is independent Date table, Date is joined with Table.  slicer is on connected Date Table axis is on disconnected date table 
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return sumx(filter( VALUES(DateInd[Date]), DateInd[Date]>=_min && DateInd[Date] <= _max),
CALCULATE(Sumx(filter(VALUES('Date'[Date]), 'Date'[Date] = max(DateInd[Date])),[Net Sales Measure]),filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ))

With both slicer 

First have a table 

Period_Table =
DATATABLE("Period", STRING,"Months", INTEGER,{
{"Last 3 Months", 3},
{"Last 6 Months", 6},
{"Last 12 Months", 12}
})

First Measure =
VAR SelectedMonths = MAX('Period_Table'[Months])
VAR _max = MAXX(ALLSELECTED(Date1), Date1[Date])
VAR _min = EOMONTH(_max, -SelectedMonths) + 1
RETURN
CALCULATE(SUM(sales[Gross Sales]),FILTER('Date','Date'[Date] >= _min &&'Date'[Date] <= _max))

Second Measure 2 =
VAR SelectedMonths = MAX('Period_Table'[Months])
VAR _max = MAX('Date'[Date])
VAR _min = EOMONTH(_max, -SelectedMonths) + 1
RETURN
CALCULATE(
SUM(sales[Gross Sales]), FILTER(ALL('Date'),
'Date'[Date] >= _min &&'Date'[Date] <= _max),USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))

Thrid Measure  =
VAR SelectedMonths = MAX('Period_Table'[Months])
VAR _max = MAX('Date'[Date])
VAR _min = EOMONTH(_max, -SelectedMonths) + 1
RETURN
SUMX(FILTER(VALUES(DateInd[Date]),DateInd[Date] >= _min && DateInd[Date] <= _max),
CALCULATE(SUMX(FILTER(VALUES('Date'[Date]),'Date'[Date] = MAX(DateInd[Date])),
[Net Sales]),
FILTER(
ALL('Date'),
'Date'[Date] >= _min && 'Date'[Date] <= _max
)
)
)

[Kavelar1]

Hi Amit,

I think what I am looking for is a measure that doesnt need to have things like [Gross Sales], [Net Sales], [Values] etc as part of the measure DAX. In that sense to avoid having to create multiple measures for each individual data I'd like to showcase. 

I could be entirely be wrong in what it is I am trying to achieve or way of thinking, but is there a possibility that I have a year and month slicer that affects the visual with a "Dynamic Date" axis to show any data attached to that visual to change based on the"Dynamic Date"? In that sense I wouldn't have to make each of the measures for all the different items I have manually, which is what I'm trying to avoid. If there is a way to apply the measures to all the different measures at once, please let me know!

Many Thanks!

[Ashish_Mathur]

Hi,

This may be possible with calcuation groups.  Share some data to work with with your basic measures ([Gross sales], [Net sales] etc.) already written.  In that file, explain via a comment/text box, your exact requirement.

[amitchandak · Accepted solution]

@Kavelar1 , the last three examples are 3 different way to create same measure that respond to both date and period for trend. If you need for more measure move the calculation to calculation group and use selectedmeasure in place of gross or net 

Power BI Tutorial: Create a 12-Month Trend with Single Slicer Selection on connected Date table- https://youtu.be/7dPrPk6LPYU

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

[Kavelar1]

Thank you this was it!