cancel
Showing results for
Did you mean:
New Member

## Calculate total up until last Sunday of the month

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?
1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6
Community Support

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

New Member

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.
Community Support

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

Super User

@nishamehrotra 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])))``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

New Member

@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

Super User

@nishamehrotra 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

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.

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!