Calculate total up until last Sunday of the month

[Anonymous]

I am wanting to calculate the total number of timesheets that were approved up until the final Sunday of the month. 

My dataset contains the total number of timesheets with a Timesheet ID, A column categorising whether the timesheet has been "approved" or not and a column with the approved date stamp. I have assigned a day number in my date table for each of the days so that Sunday = 1, Monday = 2 and so on. 

But I am not sure how to tell Power BI to calculate the DISTINCTCOUNT of timesheets submitted based on the two filters -

1. Approved status

2. To calculate up until the last Sunday of each month ONLY

This is the formula I currently have: 

Approved for Period = CALCULATE(DISTINCTCOUNT(tbl_timesheet_data[Time Report ID]),

FILTER(tbl_timesheet_data,LASTDATE(tbl_timesheet_data[Approved Date Day #] = 1) && 'tbl_timesheet_data'[Line Status] = "Approved"))

Can someone tell me what might be wrong with my formula? 

[v-kelly-msft]

Hi @Anonymous ,

Using lastdate function will not help to get the data of last Sunday for each month,check below expression:

Measure =
VAR _maxsunday =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[weekday] = 1
                && MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = _maxsunday
                && 'Table'[Status] = "Approved"
                && 'Table'[weekday] = 1
        )
    )

I have attached a sample .pbix file for reference.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hi @v-kelly-msft - thank you so much for your response. This still doesn't seem to provide me with the total timesheets approved for each month end up until the last Sunday of the month. This is my formula using your advice: 

Approved for Period =

VAR _maxsunday =

CALCULATE (

MAX (tbl_timesheet_data[Approval Date]),

FILTER (

ALL ('tbl_timesheet_data'),

tbl_timesheet_data[Approved Date Day #] = "1"

&& MONTH ([Approval Date]) = MONTH ( MAX (tbl_timesheet_data[Approval Date]) )

)

)

RETURN

CALCULATE (

DISTINCTCOUNT (tbl_timesheet_data[Time Report ID]),

FILTER (

ALL (tbl_timesheet_data),

tbl_timesheet_data[Approval Date] = _maxsunday

&& tbl_timesheet_data[Line Status] = "Approved"

&& tbl_timesheet_data[Approved Date Day #] = "1"

)

)

Could the fact that the date table and the "approved date" of the timesheet is different be a factor? 

Ideally what I would like it to show me is similar to the following: 

31.07.21 - [####]

30.08.21 - [####]

30.09.21 - [####]

31.10.21 - [####]

and those numbers for each month end will only be up until the last Sunday of that corresponding month. Anything approved after that date will go over into the next month. 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Something like below?

If so ,remove the "ALL" function in the output of the fumular:

Measure =
VAR _maxsunday =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[weekday] = 1
                && MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER (
            'Table',
            'Table'[Date] = _maxsunday
                && 'Table'[Status] = "Approved"
                && 'Table'[weekday] = 1
        )
    )

For the updated .pbix file,pls check attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Greg_Deckler]

@Anonymous Perhaps try:

Approved for Period = COUNTROWS(DISTINCT(SELECTCOLUMNS(
FILTER(tbl_timesheet_data,LASTDATE(tbl_timesheet_data[Approved Date Day #] = 1) && 'tbl_timesheet_data'[Line Status] = "Approved"),"__TimeReportID",[Time Report ID])))

[Anonymous]

@Greg_Deckler - Thank you for your reply! It still seems to be giving an error, I think it is due to the fact that PowerBI doesn't recognise "1" as a date so when we use the "lastdate" prefix, it is looking for an actual date type data, not a number. I need it to pick up the last "1" of the month which is the last Sunday 

[Greg_Deckler]

@Anonymous Try posting some sample data so we understand what is going on. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.