cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nishamehrotra
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

Hi @nishamehrotra ,

 

Something like below?

vkellymsft_0-1632274997772.png

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!

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

Hi @nishamehrotra ,

 

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!

 

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. 

Hi @nishamehrotra ,

 

Something like below?

vkellymsft_0-1632274997772.png

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!

View solution in original post

Greg_Deckler
Super User
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])))

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@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 

 

nishamehrotra_0-1631838343570.png

 

 

@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

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.

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.