Need to create a measure for month and/or week and/or day

patriciahogendo — Wed, 28 Aug 2019 19:48:49 GMT

I want to create a measure which shows the salary per selected month(s) and/or week(s) and/or day(s). My datasheet shows only the salary per person per month.

This measure works for both month and week - I can select multiple months and/or weeks and the result is as expected using this formula:

( ISFILTERED ( 'YearMonth Table'[YearMonth]),

IF(

ISFILTERED ( 'Billing Report'[Trigger Week] ),

SUM('Salary 2019'[Salary] ) * DISTINCTCOUNT( 'Billing Report'[Trigger Week] ) / 4,

SUM('Salary 2019'[Salary])

)

But I also want to add the option to filter on day. But when I add this to the one above, it doesn't work at all anymore.

I created a different formula showing all possible options and it works for a selected day or week, but not for the month anymore:

(AND

(ISFILTERED('Billing Report'[Trigger Month]),

ISFILTERED('Billing Report'[Trigger Week])),

ISFILTERED('Billing Report'[TriggerDateTime])),

DISTINCTCOUNT('Billing Report'[TriggerDateTime] ) * SUM( 'Salary 2019'[Salary] ) / 21 ,

(AND

(ISFILTERED('Billing Report'[Trigger Month]),

ISFILTERED('Billing Report'[TriggerDateTime].[Date])),

DISTINCTCOUNT( 'Billing Report'[TriggerDateTime] ) * SUM( 'Salary 2019'[Salary] ) / 21,

(AND

(ISFILTERED('Billing Report'[Trigger Month]),

ISFILTERED('Billing Report'[Trigger Week])),

DISTINCTCOUNT( 'Billing Report'[Trigger Week] ) * SUM( 'Salary 2019'[Salary] ) / 4 ,

(ISFILTERED('Billing Report'[Trigger Month]),

DISTINCTCOUNT('Billing Report'[Trigger Month]) * SUM( 'Salary 2019'[Salary] ),

(ISFILTERED('Billing Report'[Trigger Week]),

DISTINCTCOUNT( 'Billing Report'[Trigger Week] ) * SUM( 'Salary 2019'[Salary] ) / 4,

(ISFILTERED('Billing Report'[TriggerDateTime]),

DISTINCTCOUNT( 'Billing Report'[TriggerDateTime] ) * SUM( 'Salary 2019'[Salary] ) / 21,

"Shouldn't be possible" )

) ) ) ) )

Can anybody tell me what I am doing wrong here? Thanks a lot in advance!!

Re: Need to create a measure for month and/or week and/or day

Stachu — Fri, 30 Aug 2019 12:42:06 GMT

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

Re: Need to create a measure for month and/or week and/or day

patriciahogendo — Mon, 02 Sep 2019 08:23:23 GMT

Hi,

Thanks for your reply! As my dataset is quit big, I don't know how to do this.

Could you contact me via Skype (patricia.hogendoorn@damco.com ) so I can show you the whole dataset/dashboard?

Thanks a lot in advance! Patricia

Re: Need to create a measure for month and/or week and/or day

patriciahogendo — Fri, 06 Sep 2019 06:32:01 GMT

Thanks! Issue has been solved.

topic Need to create a measure for month and/or week and/or day in DAX Commands and Tips

Need to create a measure for month and/or week and/or day

Re: Need to create a measure for month and/or week and/or day

Re: Need to create a measure for month and/or week and/or day

Re: Need to create a measure for month and/or week and/or day