Group by measure with DATESBETWEEN

[obriaincian]

Hi All,

I am stuck and can't get my head around a measure I've developed.

What I want to do is create a table that shows the count of occurences within a certain month.

My Data

Product

Product	Report Date	Previous Expiry	New Expiry	Change in Dates
Coke	Jan-22	01-01-24	01-01-24	No Change
7UP	Jan-22	02-01-23	25-01-23	< 1 Month
Fanta	Jan-22	02-01-23	25-01-23	< 1 Month
Pepsi	Jan-22	01-01-24	01-01-24	No Change
Coke	Feb-22	01-01-24	01-01-24	No Change
7UP	Feb-22	25-01-23	25-03-23	No Change
Fanta	Feb-22	25-01-23	25-03-23	No Change
Pepsi	Feb-22	01-01-24	01-01-24	No Change
Coke	Mar-22	01-01-24	01-01-24	No Change
7UP	Mar-22	25-03-23	25-05-23	>1 Month
Fanta	Mar-22	25-03-23	25-05-23	>1 Month
Pepsi	Mar-22	01-01-24	01-04-24	>1 Month

So that is my data. What I want to do is Filter on the report date column for last month (which is Mar-22 or filter for the max month in the date column) and then group the Change in Dates column and count.

Desired outcome is to create a matrix visual with the below

Change	No. of occurences
No Change	1
>1 Month	3
<1 Month	0

The measure I'm currently using which doesn't work is:

CALCULATE(

COUNTA('Data'[Change in Dates]) ,DATEADD(Data[Report Date],-1,Month))

[amitchandak · Accepted solution]

@obriaincian , You need to create a date table and new dimension with distinct value Change in Dates nad join both with your table

For TI prefer the date table

CALCULATE(

COUNTA('Data'[Change in Dates]) ,DATEADD(Date[Date],-1,Month))

or

CALCULATE(

COUNTA('Data'[Change in Dates]) ,previousmonth(Date[Date]))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.

[obriaincian]

Hi @amitchandak thank you for your input. I'm just not sure how to create a date table and new dimension with distinct value Change in Dates and join both with your table

[amitchandak]

@obriaincian , New table in Dax

Change in dates = distinct(Table[Change in dates])

Date =

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)