Get sum of different dax measures and show the value only in the last day of each month

[FP220388]

Hi everyone,

I'm struggly to fix the subject....

Scenario:

I have several measures in dax which calculates times for different tasks in minutes from several tables (3 tables). All these tables have a relations with the calendar table where I also have and End of Month column, and all these tables have data for single tasks.

In another table [HourTable] I have the total amount of hours worked in a month. One row for each month with total amount of hour worked and a date column where is shown the last day of the related month. this date column is connected to the calendar as well.

From day 1 till end_of_month I assume a daily number of worked hours and I wrote a measure to calculate the "efficency" on a daily basis - this works anytime but not on the last day of the month.
So I splitted the efficency measure in two parts:

- in a variable I calculate the efficiency declared (the one at the end of month)

- in another variable I calculate the efficiency not declared the one during the month) //this works 

Below the measure:
---------------------------------------------------------------------------------------------------------------------------------------

Efficency =

VAR Staff = SELECTEDVALUE(Plant_Data[Number of Operators],SUM(Plant_Data[Number of Operators]))

VAR EfficencyIndex_Declared =    // 

    CALCULATE(

            SUMX(

                MASTER_Installation ,

                DIVIDE(

            [PM - Total Activities Time],   //PM - Total Activities Time is the measure which sum minutes from different tasks tables

            [PM - Staff Time]*60        //PM-Staff Time is a measure which sum the real hour worked

              )

            )

         )

VAR PerformanceIndex_NotDeclared =

    CALCULATE(

        SUMX(

            MASTER_Installation,

            DIVIDE(

                     [PM - Total Activities Time],

                    [PM - Expected Working Time]*60 

                    )

        )

    )

Var Perf_index =

SWITCH(

    TRUE(),

        Staff = 0, BLANK(),

        [PM - Staff Time]<>0, PerformanceIndex_Declared,

        [PM - Staff Time] = 0 && [PM - Expected Working Time]<>0, PerformanceIndex_NotDeclared,

        BLANK()

)

RETURN Perf_index

---------------------------------------------------------------------------------------------------------------------------------------

What I got if I create a trend line is this:

Can anyone help me to fix the measure on the last day of each month?

[Anonymous]

HI @FP220388,

It seems like you nested multiple measures in your formula, can you please share a pbix or some dummy data that keep the raw data structure with Dax formulas and expected results? They will help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng