Add rows if it is the last day of the month and if it is in weekdays

[memasarif]

Dear All,

I would like to sum the rows for val if they are last day of the month and if they are in the weekdays. If last day of the month corresponds to either saturday or sunday then sum friday. What could be the formula? I need e general formula to add. I have several months in my table.

Thank you. 

[v-deddai1-msft · Accepted solution]

Hi @memasarif ,

Would you please refer to the measure below:

Measure =
VAR a =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date],
            'Table'[Val],
            "weekday", WEEKDAY ( 'Table'[Date], 2 ),
            "weeknum", WEEKNUM ( 'Table'[Date] ),
            "eomonth", EOMONTH ( 'Table'[Date], 0 )
        ),
        "lastworkday",
            IF (
                'Table'[Date] = [eomonth],
                IF ( [weekday] <= 5, 1, 0 ),
                IF ( [weekday] = 5 && [weeknum] = WEEKNUM ( [eomonth] ), 1, 0 )
            )
    )
RETURN
    SUMX ( FILTER ( a, [lastworkday] = 1 ), 'Table'[Val] )

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ER3TmlaekNFKjActYx...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Greg_Deckler]

@memasarif - Thinking something like:

Measure =
  VAR __Date = MAX([DATE])
  VAR __Table = 'Table'
  VAR __Table1
    ADDCOLUMNS(
      __Table,
      "FinalVal",
        SWITCH([DAY NAME]),
          "SUNDAY",SUMX(FILTER(__Table,[DATE]=__Date-2),[VAL]),
          "SATURDAY",SUMX(FILTER(__Table,[DATE]=__Date-1),[VAL]),
          [VAL]
        )
     )

[sreenub]

Please find below Mesure to find last working day balance .

Last workinng day BalanceAmount =

CALCULATE (

SUM ( Fact[BalanceAmount] ),

FILTER ( DimDate, DimDate[Date] = MAX ( DimDate[Date] ) && DimDate[Weekdaynumber]<6)

)