Calculate the difference between the distinct count of two years

[HAW]

Hi,

I have determined the distinct count of clientnumbers for two years 2022 and 2023. For which I used two measures:

2022 = CALCULATE(

    DISTINCTCOUNT(FACT_Jeugd[Cliëntnummer]),FACT_Jeugd[Periode] = "1-1-2022 t/m 30-6-2022" || FACT_Jeugd[Periode] = "1-7-2022 t/m 31-12-2022")

2023 = CALCULATE(

    DISTINCTCOUNT(FACT_Jeugd[Cliëntnummer]),FACT_Jeugd[Periode] = "1-1-2023 t/m 30-6-2023" || FACT_Jeugd[Periode] = "1-7-2023 t/m 31-12-2023")

I would like to determine the inflow and outflow of distinct clientnumbers. If I use [2022] = blank as a filter I have the inflow and if I use [2023] is blank I have the outflow, but I would like put the outcome in one table or matrix. If I use this method I have to 2 different matrices.

So I would like to use a measure to determine the in- outflow. I have tried to determine this by making a new measure:

MEASURE = [2023] - [2022] but this determines the total of in- and outflow and I would like to make the distinction between in- outflow. In other words, a column for inflow and a column for outflow. Is there a way to do this? Or should I try something totally different?

Thanks in advance for your help!

[HAW]

Hi @DataNinja777 ,

I already have a calender table, but I have no idea how to achive the required output. 

[DataNinja777]

Hi @HAW ,

You can achive your required output in multiple ways using Power BI, but I would recommend you to utilize a calendar table and create a relationship between your fact table and the calendar table instead of using OR condition to filter the half year periods for 2022 and 2023.  A calendar table can be written in the following manner for example.  

Calendar = addcolumns(calendar(date(2022,01,01),date(2023,12,31)),"yyyy-mm",format([Date],"yyyy-mm"))

Best regards,