Last 12 Months based on Date slicer selection- and sales against them

[Jeet_Analytics]

Hi Team,

The requirement is to show the last 12 months of data in a visual, based on the date slicer selection.

Example:

i) If Date Selected in the slicer is: 15/11/2021

then the visual shall be showing the data of month range 15/11/2020 to 15/11/2021.

ii) If no Date is selected in the slicer, then the visual shall be showing the data of last 12 months from the max date available in the model.

My Approach:

When I create a measure to check if the date from which last 12 months is to be calculated as below, it works really fine:

DateFilter =

IF(

HASONEVALUE(Dates[Date]),

SELECTEDVALUE(Dates[Date]),

MAX(Dates[Date])

)

But when I try to create a calculated table as below (using the same logic) :

Date-New-TEST =

var DateFilter =

IF(

HASONEVALUE(Dates[Date]),

SELECTEDVALUE(Dates[Date]),

MAX(Dates[Date])

)

RETURN

CALENDAR(

DATE(Year(DateFilter),

MONTH(DateFilter)-12,

DAY(DateFilter)

)

,

DateFilter)

the Dax formula always considers the Else statement of the DAX written in the IF() section. i.e. somehow hasonevalue() is always returning false in the calculated table formula even when only one date value is selected. But the same logic works fine when I create a measure and check the selected value with the logic of hasonevalue() as I mentioned above.

Do we have any idea what could be the possible reason behind this?    

Thanks,

[v-shex-msft · Accepted solution]

HI @Jeet_Analytics,

Current power bi does not support creating dynamic calculated column/table based on filter/slicer selection, they are working on different data levels and you can't use child level to affect its parent.

For your scenario, I'd like to suggest you use query parameter instead, you can convert it to the query table. Then you can use Dax calculate table expression to extract that parameter and use it in the calculated table.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[amitchandak]

@Jeet_Analytics , If you select on date and need data for more that day or range, you need an independent date table 

// Independent Date table - Date1 and Joined Table Date - Last 12 Month
measure =
var _max = maxx(allselected('Date1'),'Date1'[Date]) // Use Date(2012,01,01)
var _min = Date(year(_max) -1, Month(_max), Day(_max))
return
CALCULATE(SUM(Sales[Sales Amount]),filter(Date, Date[Date]>=_min && Date[Date] <=_max ))

refer

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