Help to create a measure that calculates the upside of the diagonal

[vkisa]

Hi all,

I have two date info in my dataset and trying to full fill the upside diagonal of the table.

Sample Dataset,

Year	MonthNumber	MonthName	Customerid	Deposit_Year	Deposit_Month
2023	6	June	2956	2023	6
2023	7	July	3257	2022	12
2023	10	October	3624	2023	10
2023	1	January	3258	2022	12
2023	11	November	3629	2023	11
2023	1	January	3261	2023	1
2023	2	February	3258	2022	12
2023	7	July	2551	2022	10
2023	11	November	1052	2023	2
2023	12	December	3609	2023	5
2023	12	December	3640	2023	12
2023	4	April	3241	2022	10
2023	6	June	3609	2023	6
2023	4	April	3268	2023	3
2023	12	December	3641	2023	12
2023	9	September	2858	2023	6
2023	1	January	1859	2022	10
2023	6	June	2689	2022	10
2023	11	November	3381	2023	5
2023	6	June	3304	2023	6
2023	7	July	3593	2023	6
2023	3	March	1779	2022	10
2023	9	September	3283	2023	8
2023	9	September	1799	2022	10
2023	5	May	3302	2023	5
2023	10	October	2836	2023	1
2023	1	January	7316	2023	1
2023	11	November	2961	2023	11
2023	5	May	2867	2022	10
2023	8	August	3591	2023	6
2023	8	August	3618	2023	8
2023	8	August	2634	2023	5
2023	7	July	1044	2022	10
2023	1	January	1647	2022	11
2023	5	May	3608	2023	5
2023	3	March	3261	2023	1
2023	4	April	3265	2023	2

I can calculate the diagonal values by this measure,

Count Distinct Customer First Month of Year =
VAR selected_year= SELECTEDVALUE(table1[Year])
VAR selected_month=SELECTEDVALUE(table1[MonthNumber])
RETURN
CALCULATE(
    DISTINCTCOUNT(table1[CustomerID]),
    FILTER(table1, table1[Deposit_Year]=selected_year && table1[Deposit_Month]=selected_month)
)

But, I need to assign these diagonal values to all next months of the year like below,

- Row= table1(Deposit_Year) and table1(Deposit_Month)

- Column= table1(Year) and table1(Month)

Thank you,

Veli

[vkisa]

Hi,

I have two date info in my dataset and trying to full fill the upside diagonal of the table. 

Sample Dataset,

Year	MonthNumber	MonthName	Customerid	Deposit_Year	Deposit_Month
2023	6	June	2956	2023	6
2023	7	July	3257	2022	12
2023	10	October	3624	2023	10
2023	1	January	3258	2022	12
2023	11	November	3629	2023	11
2023	1	January	3261	2023	1
2023	2	February	3258	2022	12
2023	7	July	2551	2022	10
2023	11	November	1052	2023	2
2023	12	December	3609	2023	5
2023	12	December	3640	2023	12
2023	4	April	3241	2022	10
2023	6	June	3609	2023	6
2023	4	April	3268	2023	3
2023	12	December	3641	2023	12
2023	9	September	2858	2023	6
2023	1	January	1859	2022	10
2023	6	June	2689	2022	10
2023	11	November	3381	2023	5
2023	6	June	3304	2023	6
2023	7	July	3593	2023	6
2023	3	March	1779	2022	10
2023	9	September	3283	2023	8
2023	9	September	1799	2022	10
2023	5	May	3302	2023	5
2023	10	October	2836	2023	1
2023	1	January	7316	2023	1
2023	11	November	2961	2023	11
2023	5	May	2867	2022	10
2023	8	August	3591	2023	6

I can calculate the diagonal values by this measure,

Count Distinct Customer First Month of Year =
VAR selected_year= SELECTEDVALUE(table1[Year])
VAR selected_month=SELECTEDVALUE(table1[MonthNumber])
RETURN
CALCULATE(
    DISTINCTCOUNT(table1[CustomerID]),
    FILTER(table1, table[Deposit_Year]=selected_year && table1[Deposit_Month]=selected_month)
)

But, I need to assign these diagonal values to all next months of the year like below, 

Thank you,

Veli

[wdx223_Daniel]

Count Distinct Customer First Month of Year =
VAR selected_year= SELECTEDVALUE(table1[Year])
VAR selected_month=SELECTEDVALUE(table1[MonthNumber])
RETURN
CALCULATE(
    DISTINCTCOUNT(table1[CustomerID]),
    KEEPFILTERS(FILTER(ALL(table1), table1[Deposit_Year]=selected_year && table1[Deposit_Month]=selected_month))
)