Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

 

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 @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
Not applicable

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 @Anonymous ,

 

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!

Greg_Deckler
Super User
Super User

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

 

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.