Max date per customer per fiscal month

[Anonymous]

Hi,

Looking for a way to calculate the last visit date of customers and show it per fiscal months.

Have customers table with visit date.

Have Fiscal date table.

I want to create Last visited calculated table that will show for each customer max visit per fiscal month.

If one fiscal month visit was not made then the previous visit date should be taken.

Customers table			Fiscal Date table				Last visited calculated
Customer	Visited		Date	Fiscal Month	Year		Customer	Fiscal Month	Year	Visited
1	10.01.2019		1.01.2019	1	2019		1	1	2019	10.01.2019
1	25.03.2019		2.01.2019	1	2019		1	2	2019	10.01.2019
2	1.01.2019		3.01.2019	1	2019		1	3	2019	25.03.2019
2	4.04.2019		4.01.2019	1	2019		1	4	2019	25.03.2019
			5.01.2019	1	2019		1	5	2019	25.03.2019
			6.01.2019	1	2019		...	...	...	...
			7.01.2019	1	2019		1	13	2019	25.03.2019
			...	...	...		2	1	2019	1.01.2019
							2	2	2019	1.01.2019
							2	3	2019	1.01.2019
							2	4	2019	4.04.2019
							...	...	...	...
							2	13	2019	4.04.2019

Thank you!

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

Attached sample file for your reference.You may check the measure in it.

Regards,

[SwedeJones]

Hi @v-cherch-msft , thank you for this solution, this is brilliant, it worked just fine on my dataset! And fits my need almost perfectly. I have another column where the customer has a value as well. So if I filter e.g. April I want to know what was the sum of the latest available value for all customers. E.g. customer 1's last submitted value (say 100) was in march and customer 2 submitted a value in april that was 200. I want the result to be 300.

Is there any way to achive this? Thank you!

[SwedeJones]

I continued and seachered the web for an answer. Alberto Ferrari @SQLBI nailed it:

https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

Works great for me. The solution is quite different.

[v-cherch-msft]

Hi @Anonymous 

You may create a date table and make sure there's no relationship. Then create a measure like below:

Measure  =
VAR _date =
    CALCULATE (
        MAX ( Customers[Visited] ),
        FILTER (
            ALLSELECTED ( Customers ),
            Customers[Customer] = MAX ( Customers[Customer] )
                && MONTH ( Customers[Visited] ) = MONTH ( MAX ( 'Fixcal Date'[Date] ) )
                && YEAR ( Customers[Visited] ) = YEAR ( MAX ( 'Fixcal Date'[Date] ) )
        )
    )
RETURN
    IF (
        _date = BLANK (),
        CALCULATE (
            MAX ( Customers[Visited] ),
            FILTER (
                ALLSELECTED ( Customers ),
                Customers[Customer] = MAX ( Customers[Customer] )
                    && Customers[Visited] <= MAX ( 'Fixcal Date'[Date] )
            )
        ),
        CALCULATE (
            MAX ( Customers[Visited] ),
            FILTER (
                ALLSELECTED ( Customers ),
                Customers[Customer] = MAX ( Customers[Customer] )
                    && Customers[Visited] = _date
            )
        )
    )

Regards,

[Anonymous]

Thank you for your reply @v-cherch-msft !

I have implemented this in my project but the results are different. Not showing all months like on your picture.

Where should I look?

Again, thank you for helping out!

All the best,

Mike