Finding month no. when you have multiple records for each month, across multiple years

[BowerPI]

This query comes from one I submitted earlier (same context):

Solved: Re: Variance between months, based on filtered tab... - Microsoft Fabric Community

I've created a visual as follows:

I am trying to diagnose why but I am unable to return the month number for each of the months listed. For example Saturday, 30 April 2022, I should get the number 4. For May the number 5 etc. Currently I am getting 264 for example in April.

I've worked out that 264 is precisely the number of meter ID records in a month * month number.  So for April 2022, I have 4*66 records. For context, it means 66 meter records within Room ID: A010 in April 2022.

I used a modified version of the person's DAX from the previous query, as it was giving me some strange results. Originally I was trying to work out the rolling percentage change month to month. However, I got stuck on the month count. For April 2022 the CURRENT_MONTH evaluates to 264, while the MONTH_COUNT evaluates to 66. But when I divide it I don't get 4, instead I get 264. Same issue for the other months and Room IDs

TESTING = 
VAR CURRENT_ROOM = dcs_test_data_2[Room ID]
VAR CURRENT_MONTH = dcs_test_data_2[month].[MonthNo]
VAR CURRENT_YEAR = dcs_test_data_2[month].[Year]
VAR PREVIOUS_MONTH = CURRENT_MONTH - 1
VAR PREVIOUS_consumption = 
CALCULATE(
    SUM(dcs_test_data_2[consumption]),
    FILTER(
        dcs_test_data_2,
        dcs_test_data_2[Room ID] = CURRENT_ROOM && dcs_test_data_2[month].[MonthNo] = PREVIOUS_MONTH && dcs_test_data_2[month].[Year] = CURRENT_YEAR
    )
)

VAR MONTH_COUNT = 
CALCULATE(
    DISTINCTCOUNT(dcs_test_data_2[month].[MonthNo]),
    FILTER(
        dcs_test_data_2,
        (dcs_test_data_2[Room ID] = CURRENT_ROOM && dcs_test_data_2[site_name] = "siteA" && dcs_test_data_2[month].[MonthNo] = CURRENT_MONTH && dcs_test_data_2[month].[Year] = CURRENT_YEAR)
    )
)
RETURN
DIVIDE(CURRENT_MONTH, MONTH_COUNT)

Thank you 

[BowerPI]

bumb

[v-heq-msft]

Hi @BowerPI ,
From your description, it seems like the variable is incorrectly capturing the product of the month number and the count of records, rather than the month number itself. This is likely due to the way the variable is being calculated.
You can try the follwing dax to troubleshoot your problem:

TESTING = 
VAR CURRENT_ROOM = SELECTEDVALUE(dcs_test_data_2[Room ID])
VAR CURRENT_MONTH = MONTH(SELECTEDVALUE(dcs_test_data_2[Date]))
VAR CURRENT_YEAR = YEAR(SELECTEDVALUE(dcs_test_data_2[Date]))
VAR PREVIOUS_MONTH = CURRENT_MONTH - 1
VAR PREVIOUS_consumption = 
CALCULATE(
    SUM(dcs_test_data_2[consumption]),
    FILTER(
        dcs_test_data_2,
        dcs_test_data_2[Room ID] = CURRENT_ROOM && MONTH(dcs_test_data_2[Date]) = PREVIOUS_MONTH && YEAR(dcs_test_data_2[Date]) = CURRENT_YEAR
    )
)
RETURN
CURRENT_MONTH

Best regards,

Albert He

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

[BowerPI]

No luck unfortunately, I just get blank table:

Any other ideas?