Forum Discussion

Kebas_Leech's avatar
Kebas_Leech
Helper I
3 years ago

Row context problem

Hello everyone,

 

I have a daily target measure that calculates the targets for each day of the month based on a fixed monthly target: (the daily target is always the same each day of that specific month).

So i had to calculate the [NumberDaysMonth] and filter it according to the respective holidays of that month.

Total NumberDaysMonth = 
Var TotalDays =
SUMX (
    SUMMARIZE (
        ADDCOLUMNS (
            VALUES ( Tab_Origem[Data] ),
            "EOMonth", EOMONTH ( Tab_Origem[Data], 0 )
        ),
        [EOMonth]
    ),
    DAY ( [EOMonth] )
)
Var NrHolidays = 
   SUMX(
    SUMMARIZE(
        Tab_Origem,
        Tab_Origem[Data],
        Tab_Origem[Holidays],
        "Count", COUNTROWS(Tab_Origem)
    ),
    IF( Tab_Origem[Holidays] = "Yes", [Count], 0)
)

RETURN TotalDays-NrHolidays

  

My problem is that it calculates on a row filter context based on the holiday day (1st of May). I need all remaining May days to have the same number as the respective lowest value calculated. In my example its 30 so I need all May days  to be 30. If it had two holidays it needed to be 29, and so on.

 

How can I manage that? 

Thank you.

5 Replies

    • Kebas_Leech's avatar
      Kebas_Leech
      Helper I
      3 years ago

      Doesnt work. At least for two reasons:

      1) I only have listed working days in my database
      2) and even if a consider the following code:

       

      Var Firstday = STARTOFMONTH(Tab_Origem[Data])
Var EndDay = ENDOFMONTH(Tab_Origem[Data])
Var HolidayDate = CALCULATETABLE(VALUES(Tab_Origem[Data]), 'Tab_Origem'[Holidays] = "Yes")

Var Net =
    NETWORKDAYS(Firstday, EndDay, 1, HolidayDate)

       

       It does the same mistake/problem that I had in the first post, and that is : Whenever the Tab_Origem[ Holidays] is "Yes" the corresponding date always has one less total day. Using the code i wrote here , the result is:

      As you see the May 1st as one less day.

      I want, in May , all days to be 30 because i only have 1 holiday in this month and all the other are workingdays.

  • devanshi's avatar
    devanshi
    Helper V
    3 years ago

    CountHolidays = 
    VAR holidays = MINX(FILTER('TableName', [Holiday] = True), COUNT('TableName'))
    VAR selectedmonth = CALCULATE(COUNTROWS('TableName'), [Month] = SELECTEDVALUE[Month] &&  [Hloday] <> True)
    RETURN
     IF([Holiday] = True, holidays, selectedmonth)