How to calculate a measure that uses days in month but for a year

[Mburman-07]

Hi All,

I have the below measure that multiplies by number of days in Month.

TNUoS_Measure = (SUM('F_BATTERY_NON_TRADER_REVENUES'[TNUoS charges per SP])*48)*SELECTEDVALUE(DateTime_Calendar[Days in Month])

However, when I want to look at multiple months worth, or for a whole year. The measure returns blank. Screenshot below. My question is how would I calculate this measure but for more than one month/for a year.

Multiple months

Single Month 

[v-xianjtan-msft]

Hi @Mburman-07 

First of all, thanks to johnt75, ajohnso2 and Bibiano_Geraldo for their great replies to this question.

May I ask if their replies has helped you solve your problem? If so, please consider accepting the reply that helped you as a solution, which will benefit users experiencing similar problems.

If you have any other questions, please feel free to contact me.

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

[Bibiano_Geraldo]

Hi @Mburman-07 ,

The issue arises because SELECTEDVALUE() returns a value only if a single value is selected in the column. When you select multiple months or a year, the context no longer contains a single value for DateTime_Calendar[Days in Month], causing SELECTEDVALUE() to return BLANK().

To handle this, you can use the bellow DAX:

TNUoS_Measure = 
SUM('F_BATTERY_NON_TRADER_REVENUES'[TNUoS charges per SP]) * 48 * 
SUMX(
    VALUES(DateTime_Calendar[MonthYear]),
    MAX(DateTime_Calendar[Days in Month])
)

If you need a sum of the total days across all selected months (e.g., for overlapping or partial months you can use bellow DAX:

TNUoS_Measure = 
SUM('F_BATTERY_NON_TRADER_REVENUES'[TNUoS charges per SP]) * 48 * 
SUM(DateTime_Calendar[Days in Month])

I hope this help you, if yes please give a Kudo and accept as solution.

Thank you

[ajohnso2]

First i created a measure to count the number of selected (month slicer) days in each month.

Days in Month =

COUNTX(VALUES('Calendar'[Date]),'Calendar'[Date])

If you already have this then great.

For the multiplier column i used the below DAX

BasexNumDays =

[Amount] * [Days in Month]

 

[johnt75 · Accepted solution]

I think you can use

TNUoS_Measure =
SUMX (
    VALUES ( DateTime_Calendar[Year Month] ),
    CALCULATE (
        SUM ( 'F_BATTERY_NON_TRADER_REVENUES'[TNUoS charges per SP] ) * 48
            * SELECTEDVALUE ( DateTime_Calendar[Days in Month] )
    )
)

[Mburman-07]

Hi @johnt75,

Thank you for this, where you have 

VALUES ( DateTime_Calendar[Year Month] )

Are you expecting a value of say October 2024 for example?

[johnt75]

Yes, precisely. I usually have a column in the date table which is the start of the month as a date, formatted to display as e.g. "Oct 2024". Very useful in slicers and visuals when you don't want to report down to the day level.