Need to create a dynamic Calculated Column/Measure for counting values based on selected value

[Anonymous]

I have data similar to the below sample-

Above is the source data. Each 'Name' has values for multiple quarters and for each quarter its either approved or not.

I need to create a dynamic measure/calculated column where based on the selection 'Quarter' in Power BI the previous 4 Quarters along with the selection need to considered for a 'Name' and the count of 'Approved' with status 'Y'. 

For eg., if the selection is '2021 Q4' then the calculation should consider the Quarters - 2021 Q4,2021 Q3,2021 Q2,2021 Q1 and the count of 'Approved=Y'. For '2021 Q4' selection of Name 'ABC' the count is '1'.

The display in Power BI would look something like this:

I think some iterative calculation needs to be done and added to a loop. Kindly help with creating the right logic for this. Also the previous Quarter selection should be dynamic and if there are no values in case '2021 Q1' is selected then only that needs to be taken for the calcuation since there are no prior values.

[Jihwan_Kim]

Approval count during last 4 periods : =
VAR _lastperiod =
MAX ( 'Calendar'[OffsetNumber_Quarter] )
VAR _selectedperiod =
SELECTEDVALUE ( 'Calendar'[OffsetNumber_Quarter], _lastperiod )
VAR _lastfourperiod =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[OffsetNumber_Quarter] > _selectedperiod - 4
&& 'Calendar'[OffsetNumber_Quarter] <= _selectedperiod
)
RETURN
CALCULATE (
COUNTROWS ( FILTER ( Data, Data[Approved] = "Y" ) ),
_lastfourperiod
) + 0

If your dim-date table has a kind of offset column for quarter periods, please try similar to the above.

Or, please share your sample pbix file, then I can try to look into it to come up with an accurate measure.

thanks.

 Link to the sample pbix file 

[Anonymous]

Hi Kim,

               Thanks for your help. I just need to implement your solution to this data below. All the components need to be in the same table and that way my measure will be simple and not reference multiple items. Could you implement your solution into this single data table. I am not able to attach the PBIX file but i can give the data i want the solution for.

Name	Quarter	Approved	Date
ABC	2021 Q1	N	01-01-21
ABC	2021 Q2	Y	01-04-21
ABC	2021 Q3	N	01-07-21
ABC	2021 Q4	N	01-10-21
ABC	2020 Q1	Y	01-01-20
ABC	2020 Q2	Y	01-04-20
ABC	2020 Q3	N	01-07-20
ABC	2020 Q4	N	01-10-20

[Jihwan_Kim]

Hi, please try the below.

But if possible, please try to use proper dim calendar table in your data model.

Anyway, the below is only using one table as you mentioned.

Count of Approvals : =
COUNTROWS (
FILTER (
ALL ( Data ),
Data[Approved] = "Y"
&& Data[Date] <= MAX ( Data[Date] )
&& INT ( Data[Date] )
> INT ( MAX ( Data[Date] ) ) - 365
)
)

Sample pbix file 

[Anonymous]

Hi Kim,

               Thanks so much for your help. This solution worked for the sample set data i gave you where there was a single 'Name' which is 'ABC'.

But when i tried the solution for multiple Names, the output was wrong. Can you let me know how to update the query for the below updated dataset:

Name	Quarter	Approved	Date
ABC	2021 Q1	1	01-01-21
ABC	2021 Q2	0	01-04-21
ABC	2021 Q3	1	01-07-21
ABC	2021 Q4	1	01-10-21
ABC	2020 Q1	1	01-01-20
ABC	2020 Q2	0	01-04-20
ABC	2020 Q3	0	01-07-20
ABC	2020 Q4	0	01-10-20
XYZ	2021 Q1	0	01-01-21
XYZ	2021 Q2	1	01-04-21
XYZ	2021 Q3	0	01-07-21
XYZ	2021 Q4	1	01-10-21
XYZ	2020 Q1	0	01-01-20
XYZ	2020 Q2	1	01-04-20
XYZ	2020 Q3	1	01-07-20
XYZ	2020 Q4	1	01-10-20
PQR	2021 Q1	0	01-01-21
PQR	2021 Q2	1	01-04-21
PQR	2021 Q3	1	01-07-21
PQR	2021 Q4	1	01-10-21
PQR	2020 Q1	0	01-01-20
PQR	2020 Q2	1	01-04-20
PQR	2020 Q3	0	01-07-20
PQR	2020 Q4	0	01-10-20

[Jihwan_Kim · Accepted solution]

Hi, 

Sorry to say again, but it will be more easier and efficient if you can create dim-calendar table and other relevant dimension tables.

However, I assume that you might have a reason that you cannot create dimension tables.

Please check the below if it works.

Link to the sample pbix file 

Count of Approvals : =
SUMX (
VALUES ( Data[Name] ),
CALCULATE (
COUNTROWS (
FILTER (
ALL ( Data ),
Data[Name] = MAX ( Data[Name] )
&& Data[Approved] = 1
&& Data[Date] <= MAX ( Data[Date] )
&& INT ( Data[Date] )
> INT ( MAX ( Data[Date] ) ) - 365
)
)
)
)

[Anonymous]

Thank you so much for your help, Kim. 

Actually the data model is in Azure and i dont have the freedom to create multiple tables as in a Power BI Dashboard. But with your help i was able to achieve my requirement.

[selimovd]

Hey @Anonymous ,

a calculated column is static, so there is no dynamic change based on a slicer.

If you use a measure, the following should give you the result:

Count of Approvals =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[Approved] = "Y",
    DATESINPERIOD(
        'DateTable'[Date],
        MAX( 'DateTable'[Date] ),
        -4,
        QUARTER
    )
)

Be aware that you need a proper date table in order for the time intelligence functions to work. Also you should filter on the date from the date table and not for the date in your fact table.

Here is a small tutorial how to create a date table:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi Denis,

                   Thanks for the help!

                    I am favcing an issue as soon as i use the filter condition.

Once that values is given the Countrows funtion takes only the rows for that filtered selection.

But i need to be able to count for the last 4 quarters based on the selected quarter. I tried using selected values in the measure but still i am not getting the correct count.

For the below measure the count should be 4, but the measure counts it as 1:

Measure = CALCULATE(COUNTROWS(Sheet1),

DATESINPERIOD(

Sheet1[Date],SELECTEDVALUE(Sheet1[Date]),-4,QUARTER))

[selimovd]

Hey @Anonymous ,

the time intelligence functions like DATESINPERIOD work only if you have a a proper date table. If you don't have a proper date table the functions will not work.

Here is a small tutorial how to create a date table:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic