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