Help with DAX writing for different KPI's based on date periods.

[Anonymous]

Our Clinicians have a KPI for their billable hours per fortnight based on their available hours. Their KPI is 20% lower during school holidays.

In my calendar table I have created a column to add the school holidays by date for next year, see attached.

I have two measures that calculates the clinicians reached target% based on their available hours, billable hours and their personal KPI. Base KPI measure is “Reached Target%” and the school Holiday KPI measure is “SH reached Target%”.

The calculations are correct and it works when I add both measures into the chart.

Can someone please help me with the next step to write a dax that will calculate “SH Reached Target%” when CalendarTable.SchoolHoliday = “SH” else calculate “Reached Targets%"

Please let me know if you need further information.  

 Your help is much appreciated. Amanda

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

If School Holiday is column:

Measure =
IF (
    MAX ( 'CalendarTable'[School Holiday] ) = "SH",
    [SH reached Target %],
    [Reached Target %]
)

If School Holiday is measure:

Measure 2 =
IF ( [SchoolHoliday Measure] = "SH", [SH reached Target %], [Reached Target %] )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[amitchandak]

@Anonymous , Try like

calculate([SH Reached Target%], filter(CalendarTable, isblank(CalendarTable[SchoolHoliday] )))

[Anonymous]

Thanks, but that will not have the measure calculate to [Reached Target%] when Blank. 

What I need is someting like this but than in a measure:

IF(CalendarTable[School Holiday] = "SH" , [SH reached Target %], [Reached Target %]

[amitchandak]

@Anonymous , Is calendar table related to the table you created SH Reached Target%](based column table) ?

If yes try like 

calculate([SH Reached Target%], filter(CalendarTable, CalendarTable[SchoolHoliday] = "SH"))

else

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Yes they are related and this works partly. When it is not "SH" this measure returns blank but I want it to calculate  [Reached Target%] when (CalendarTable, CalendarTable[SchoolHoliday] = BLANK()) 

Basiscally I need an else statement in this measure somehow. 

Thanks