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

[patriciahogendo]

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:

IF

( 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:

IF

(AND

(AND

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

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

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

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

IF

(AND

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

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

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

IF

(AND

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

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

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

IF

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

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

IF

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

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

IF

(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!!

[Stachu]

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

[patriciahogendo]

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

[patriciahogendo · Accepted solution]

Thanks! Issue has been solved.