Dax for calculating the unutilized Intents according to the year month and week-wise.

[mannyjha7860]

Hi,
I need a Dax for calculating the unutilized Intents according to the year month and week-wise.

so if total intent is 19 and on the month of October if total intent used is 7 then I should get unutilized 12 intents (19 intent count-7 used intent =12 unutilized intent  )
below is the example table

[v-zhangti · Accepted solution]

Hi, @mannyjha7860 

Please check the following methods.

unutilized intents month = 
CALCULATE (
    19 - SUM ( 'Table'[intent utilized] ),
    FILTER ( 'Table', [date].[Month] = EARLIER ( 'Table'[date].[Month] ) )
)

weeknum = WEEKNUM([date],2)

unutilized intents week = 
CALCULATE (
    19 - SUM ( 'Table'[intent utilized] ),
    FILTER ( 'Table', [weeknum] = EARLIER ( 'Table'[weeknum] ) )
)

Is this the result you expected?

Best Regards,

Charlotte Zhang

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

[amitchandak]

@mannyjha7860 , Create a new column

new column =
var _month = eomonth([Date],0)
return
19 - sumx(filter(Table, eomonth([Date],0) =_month) ,[intent untlized])

or a new measure like

new measure =
var _month = eomonth(max(Table[Date]),0)
return
19 - sumx(filter(allselected(Table), eomonth(Table[Date],0) =_month) ,[intent untlized])