DAX Calculate where column text is IN another scalar measure

[powerbi1980]

I am trying to create a compare to previous period report, and having some issues with a measure to calculate amounts based on the previous periods. 

Very simple dashboard with one slicer on it. The slicer is for the Fiscal Period (ex. 202301, 202302, 202303 etc).  users can select multiple periods to see the selected periods amounts. I would then like to do a measure to get the previous period amounts, like this: 

Previous Period Amount = CALCULATE( SUM(FactGL[DisplayAmount]) , DimPeriod[Period] IN [Selected Previous Periods] /*Ex. '202201,202202,202203' */ )

Selected Previous Periods is a measure that does the following (returns a string containing all the periods selected in the slicer as a scalar value, converted to their previous year equivalents. So if user selects periods 202301 and 202302 in the slicer, this measure returns "202201, 202202" as a string)

Selected Previous Periods = CONCATENATEX ( VALUES ( DimPeriod[Period] ) , CONCATENATE(LEFT([Period], 4) - 1, RIGHT([Period],2)), ",")

However, I recognize that "DimPeriod[Period] IN [Selected Previous Periods] /*Ex. '202201,202202,202203' */" won't work because IN is reserved for tables. I've tried to use below (Search), but to no luck.

Previous Period Actual Amount = CALCULATE( SUM(FactGL[DisplayAmount]) , SEARCH( DimPeriod[Period],[Selected Previous Periods]) > 0 /*Ex. '202201,202202,202203' */ )

Any suggestions? At its core, I'm just trying to say in my measure filter "WHERE Column Value IN text string from scalar measure"

[v-jianboli-msft · Accepted solution]

Hi @powerbi1980 ,

Based on your description, I have created a simple sample:

Please try:

Previous Period Amount = 
var _a = SELECTCOLUMNS('DimPeriod',"Period",[Period]-100)
return CALCULATE(SUM(FactGL[DisplayAmount]),FILTER(ALL(DimPeriod),VALUE('DimPeriod'[Period]) IN _a))

Final output:

Best Regards,

Jianbo Li

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

[amitchandak]

@powerbi1980 , what is basis of previous perdio month of year. Make sure you are using date table and slicer, measure and visual are using field from date table

you can have measure like

example

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

You want select and compare

How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510