Assign Same Value to All Months of the Year in DAX

Question

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
2023	6	June	3312	2023	5
2023	5	May	1830	2023	1
2023	9	September	3249	2023	9
2023	4	April	2905	2022	10
2023	8	August	3590	2023	6
2023	5	May	3291	2023	5
2023	10	October	3612	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, 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

Answer 1

You will need a separate "Month" table to be used in the Matrix column.
Create a new Table with the following:

Months = 
  SELECTCOLUMNS(
    GENERATESERIES(1, 12),
    "MonthNum", [Value],
    "Month", FORMAT( DATE(2020, [Value], 1), "MMMM"),
    "Month short", FORMAT( DATE(2020, [Value], 1), "MMM")
  )

Add Months'[Month] as your second column to the Matrix.

Then update your measure to:

Count Distinct Customer First Month of Year =
  var y = SELECTEDVALUE(table1[Year])
  var m = SELECTEDVALUE(Months[MonthNum]) // <-- new month table
  var yd = SELECTEDVALUE(table1[Deposit_Year])
  var md = SELECTEDVALUE(table1[Deposit_Month])
  RETURN
    CALCULATE(
      DISTINCTCOUNT(table1[CustomerID]),
      table1[Year] = yd && table1[MonthNumber] = md &&
      yd = y && md <= m
    )