DAX Calculate datesinperiod and filter

[JolienR]

I'm trying to count the number of customers in the period 12-6 months ago, however I only want to count the max date per customer. I have the following formula, however this is not working: 

MaxDate/Customer12-6Month =

VAR _MaxDate = max('Calendar'[Date])

VAR _StartDate = MINX(FILTER(DATESINPERIOD('Calendar'[Date]; _maxDate; -6; MONTH ); 'Calendar'[Date]);'Calendar'[Date])

VAR _ID = MAX('table'[ID_CUSTOMER_GUID])

RETURN

CALCULATE(DISTINCTCOUNTNOBLANK('table'[ID_CUSTOMER_GUID]);

FILTER('table';table'[DATES]=[LastDate12-6Month]);

DATESINPERIOD('Calendar'[Date];_StartDate;-6;MONTH))

[Anonymous]

Hi @JolienR ,

Do you want to calculate data from six months ago to one year ago or from six months to one year in the future?

What do you want the output to look like in the picture you gave?Do you wish to calculate it 6-12 months ago based on the selected date?

Please have a try.

Measure = VAR _6MONTH=EDATE([LAST DATE],6)
VAR _12MONTH=EDATE([LAST DATE],12)
 VAR _1= CALCULATE(MAX('Table'[dates]),FILTER(ALL('Table'),'Table'[customer]=SELECTEDVALUE('Table'[customer])&&'Table'[dates]>=_6MONTH&&'Table'[dates]<=_12MONTH))
RETURN
IF(_1=SELECTEDVALUE('Table'[dates]),_1,BLANK())

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[JolienR]

Thanks for your respons. 
I want to DISTINCTCOUNT the number of customers 6 to 12 months ago. However, I only want to count the last visitdate per customer. 

[Anonymous · Accepted solution]

Hi @JolienR ,

Have a try.

easure = VAR _6MONTH=EDATE([LAST DATE],-6)
VAR _12MONTH=EDATE([LAST DATE],-12)
 VAR _1= CALCULATE(MAX('Table'[dates]),FILTER(ALL('Table'),'Table'[customer]=SELECTEDVALUE('Table'[customer])&&'Table'[dates]<=_6MONTH&&'Table'[dates]>=_12MONTH))
RETURN
IF(_1=SELECTEDVALUE('Table'[dates]),_1,BLANK())

If it does not help, please provide a screenshot of the required output for the image you provided.

Best Regards
Community Support Team _ Rongtie

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

[JolienR]

Hi @Anonymous ,
Thank you for your response, however it does not help.

One customer can have visit our locations multiple times in the selected period. However, I just want the count for each customer only the last visit in the period from 12 to 6 months ago. I have attached a test file. Hopefully it is clear lik this:

I have a working measure for the current selected month, namely:

CALCULATE(DISTINCTCOUNTNOBLANK('table'[ID_CUSTOMER_GUID]);

FILTER('table';table'[DATES]=[LastDate])).

However for the period from 12-6 months the combination Filter && Dates in period is not working. 

CALCULATE(DISTINCTCOUNTNOBLANK('table'[ID_CUSTOMER_GUID]);

FILTER('table';table'[DATES]=[LastDate12-6Month]);

DATESINPERIOD('Calendar'[Date];_StartDate;-6;MONTH))

[Anonymous]

Hi @JolienR ,

Please have a try.

measure =
VAR _1 =
    TODAY ()
VAR _12monthago =
    EDATE ( _1, -12 )
VAR _6monthage =
    EDATE ( _1, -6 )
RETURN
    CALCULATE (
        MAX ( calendar[date] ),
        FILTER (
            ALL ( table ),
            table[date] >= _12monthago
                && table[date] <= _6monthage
                && table[customer] = SELECTEDVALUE ( table[customer] )
        )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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