calculate measure shows results blank

[Anonymous]

Hi,

I created a measure to get the previous value from the slicer below is the measure,

extract =

var q = SELECTEDVALUE('Table'[YYYQQ])

var subs = SUBSTITUTE(q,"Q",0)

var subsn = VALUE(subs)

//return previousquarter

var previousq = CALCULATE(MAX('Table'[quarterYY]),FILTER(ALL('Table'),'Table'[quarterYY] < subsn ))

//return previousq

return previousq

*****

and after passing the above measure in calulate function( below) to obatin value from another colum(YYYQQ) it just gives blank though the value is present. when i hardcode the same value in calculate measure it will works fine.

value =

CALCULATE(MAX('Table'[YYYQQ]),FILTER(ALL('Table'),'Table'[quarterYY]=[extract]))

 Below is the simple data set

DTColumnyearYYYQQquarterquarterYY

Tuesday, February 1, 2022	1	2022	2022Q1	1	202201
Saturday, January 1, 2022	1	2022	2022Q1	1	202201
Tuesday, March 1, 2022	1	2022	2022Q1	1	202201
Friday, April 1, 2022	2	2022	2022Q2	2	202202
Sunday, May 1, 2022	2	2022	2022Q2	2	202202
Wednesday, June 1, 2022	2	2022	2022Q2	2	202202
Wednesday, February 1, 2023	1	2023	2023Q1	1	202301
Sunday, January 1, 2023	1	2023	2023Q1	1	202301
Wednesday, March 1, 2023	1	2023	2023Q1	1	202301
Saturday, April 1, 2023	2	2023	2023Q2	2	202302
Monday, May 1, 2023	2	2023	2023Q2	2	202302
Thursday, June 1, 2023	2	2023	2023Q2	2	202302
Saturday, July 1, 2023	3	2023	2023Q3	3	202303
Tuesday, August 1, 2023	3	2023	2023Q3	3	202303
Friday, September 1, 2023	3	2023	2023Q3	3	202303

Please advise. 

Regards

Bharath

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table as slicer.

Table 2 =
DISTINCT('Table'[YYYQQ])

2. Create measure.

extract =
var q = SELECTEDVALUE('Table 2'[YYYQQ])
var subs = SUBSTITUTE(q,"Q",0)
var subsn = VALUE(subs)
var previousq = CALCULATE(MAX('Table'[quarterYY]),FILTER(ALL('Table'),'Table'[quarterYY] < subsn ))
return
previousq

value =
CALCULATE(MAX('Table'[YYYQQ]),FILTER(ALL('Table'),'Table'[quarterYY]=[extract]))

3. Result:

Use [YYYQQ] of Table2 as a slicer.

Best Regards,

Liu Yang

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

[amitchandak · Accepted solution]

@Anonymous , Create a date table and join it with your date of your Table 

hen you can have measures like

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Or create a Rank column (again in date column)

Qtr Rank = RANKX(all('Date'),'Date'[Year Qtr],,ASC,Dense) //Sortable Year Qtr

and have measures like
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s