Query IDs from previous quarter

[Anonymous]

I'm trying to make a DAX query which can pull all the customer ID from previous quarter and their respective data columns based on slicer selection but I'm not able to figure out the right function to use here. I've tried using SUMMARIZECOLUMNS and GROUPBY after looking up at some examples but the syntax doesn't seem to be correct.

For Eg: If a user select EffectiveDate: December, 31, 2020 from the dropdown, the table should have IDs of previous quarter ( EffectiveDate: September, 30, 2020). I know how to do it in sql/python but not sure what is the right approach/function to dynamically query rows based on filter selection.
These are the final table columns I'm trying to extract. I've attached the pbix file for reference. The table I have used in filtering the current quarter selection and not dynamic.

 Note: EffectiveDate will always be last day of quarter in my case.

PBIX Link: https://drive.google.com/file/d/1A-l-uQpk3enSsAOn5IfFux4NcRE18p_V/view?usp=sharing

Let me know if there's any way to achieve this.

Thanks

[amitchandak]

@Anonymous , Try Time Intelligence with date table, example

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

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thanks for replying @amitchandak , I've already tried Time intelligence function as you suggested. But, it won't solve the problem. Time intelligence and Date table will only provide the logic for filtering but I'm trying to retrieve rows from previous quarter and I want to know what's the right DAX function which can do that.

[Anonymous]

@Anonymous 

What you're trying to do requires one thing only: a logical measure that returns True (1) when a row from the underlying table qualifies and False (0) otherwise. Once you've got this measure, you can use the Filter Pane to set a condition for row visibility based on the measure (which, of course, will be reacting to your silcer selections). Note that real logical functions (i.e., such that really return the bool values) will not work in the Filter Pane. You have to return an integer 0/1 from them.

[Anonymous]

@Anonymous Thanks for replying. Based on your guidance, I created the following DAX measure:

row select = 
VAR _d1 = SELECTEDVALUE(Sheet1[EffectiveDate])
RETURN IF(MAX(Sheet1[EffectiveDate])< _d1,1,0)

If the EffectiveDate for current row is less than the selected EffectiveDate, then the returned value is 1 or else 0. But the MAX doesn't seem to work on table level instead of row level. Can you please suggest the appropriate modification (or correct function to use) so the flags are calculated on row level.

[Anonymous · Accepted solution]

@Anonymous 

Your Effective Date field must not filter the table from which you bring data into the visual for this to work. It's obvious why this should be so. If your selection filters data in the table, then... your visual will only show the data visible in the current context or a subset of it, not a superset. There are different ways to make it work. I can't see the model, so I'll tell you how I'd most likely do it. I would create a separate table with Effective Dates only that would be either disconnected from the fact table or had an inactive relationship with the fact table. Then I would create a slicer out of Effective Dates and the measure. Let's assume that the table is disconnected. The measure would be:

// Best Practice: All columns in a fact table
// MUST be hidden and slicing is performed only
// through dimensions. Deviate from this and...
// you'll be in for nasty suprises sooner or
// later.

[Show Row] =
// Efective Dates must be a proper date table
// marked as a date table in the model. If
// you want to show only certain dates to the
// user in a slicer you can have a field in
// the table that will enable you to filter
// for the dates in the slicer visual.
// I assume here that you are placing the Date
// field in the slicer and this in fact is an
// effective date. Any other setup and you must
// adjust the code accordingly.
var vIsOneEffectiveDateSelected = HASONEVALUE( 'Effective Dates'[Date] )
// Assuming that ID's are in the fact table, FT.
var vOneIdIsInScope = ISINSCOPE( FT[ID] )
var vResult =
    if( vOneIdIsInScope && vIsOneEffectiveDateSelected,
        var vCurrentEffectiveDate = SELECTEDVALUE( FT[EffectiveDate] )
        var vPrevQtrStart =
            MIN( PREVIOUSQUARTER( 'Effective Dates'[Date] ) )
        var vPrevQtrEnd =
            MAX( PREVIOUSQUARTER( 'Effective Dates'[Date] ) )
        var vIDQualifies =
            vPrevQtrStart <= vCurrentEffectiveDate
            &&
            vCurrentEffectiveDate <= vPrevQtrEnd
        RETURN
            vIDQualifies
    )
return
    1 * vResult

[Anonymous]

It's easy to fix. Instead of the above, you can use

MAXX( PREVIOUSQUARTER( 'Effective Dates'[Date] ), 'Effective Dates'[Date] )

Same with MIN: just replace MAXX with MINX in the above line.

[Anonymous]

Perfect. Thanks a lot for your inputs @Anonymous 

[Anonymous]

Thanks for the detailed explanation @Anonymous , I followed the steps you mentioned but the MIN function only accepts column as an argument while the PREVIOUSQUARTER function return type is a Table. This is throwing a DAX syntax error. I've also attached the pbix file in case you want to refer the data model: 

https://drive.google.com/file/d/1AVPPaYVleWg7dQrsSz9THw02RR-4jTz2/view?usp=sharing

Thanks