Dateinperiod with -1 month, -6 month, -12 month

[alsolok]

Hello everyone,

I have the problem with this measures.

I went to calculate the distinct count customers with period interval for 1 month:

Nbre customers validation < 1 month:= CALCULATE(distinctcount('Customers'[id_client]), 'Customers'[id_principal] = BLANK(), FILTER('Customers','Customers'[Nbre_valid]>0), DATESINPERIOD(
'Customers'[date_photo],
MAX('Customers'[date_photo]),
-1,
MONTH
))

For 6 month

Nbre customers validation < 1 month:= CALCULATE(distinctcount('Customers'[id_client]), 'Customers'[id_principal] = BLANK(), FILTER('Customers','Customers'[Nbre_valid]>0), DATESINPERIOD(
'Customers'[date_photo],
MAX('Customers'[date_photo]),
-6,
MONTH
))

For 12 month

Nbre customers validation < 1 month:= CALCULATE(distinctcount('Customers'[id_client]), 'Customers'[id_principal] = BLANK(), FILTER('Customers','Customers'[Nbre_valid]>0), DATESINPERIOD(
'Customers'[date_photo],
MAX('Customers'[date_photo]),
-12,
MONTH
))

Customers[date_photo] is the column the table fact is the relation with dimension table Date.

The Dim Table Date is the filter in the segment(power bi) not interaction with the measures -1, 6 and 24 month

[amitchandak · Accepted solution]

@alsolok , You need to create a date table table first and join with you Table table

Create these measures

M1= CALCULATE(distinctcount('Customers'[id_client]), 'Customers'[id_principal] = BLANK(), FILTER('Customers','Customers'[Nbre_valid]>0) )

then

Rolling 12 = CALCULATE([M1],DATESINPERIOD('Date'[Date ],MAX(Date[Date]),-12,MONTH))

Rolling 6 = CALCULATE([M1],DATESINPERIOD('Date'[Date ],MAX(Date[Date]),-6,MONTH))

or you can use date from you table as max

Rolling 12 = CALCULATE([M1],DATESINPERIOD('Date'[Date ],MAX('Customers'[date_photo]),-12,MONTH))

Rolling 6 = CALCULATE([M1],DATESINPERIOD('Date'[Date ],MAX('Customers'[date_photo]),-6,MONTH))

You can use what if to have dynamic number 1, 6, 12 etc

Rolling Months Formula: https://youtu.be/GS5O4G81fww

https://docs.microsoft.com/en-us/power-bi/desktop-what-if