Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I was lookin got get a count of a data until last saturday of the month.
Is there any dax available for it?
My approach is to first get the date of the last Saturday of each month, and then do a count operation
My test table:
Create a column represents the first day of the next month
FirstDayOfNextMonth = DATE(YEAR([DATE]),MONTH([DATE])+1,1)
Then create a coumn represents the lastSaturday of this month
LastSaturday = DATEADD('TableDEMO'[FirstDayOfNextMonth].[Date],-WEEKDAY([FirstDayOfNextMonth]),DAY)
Now the table looks like this :
Then I create a column to count
Column = CALCULATE(COUNT(TableDEMO[DATE]),FILTER(ALL(TableDEMO),MONTH([DATE])=MONTH(EARLIER(TableDEMO[DATE])) && [DATE]<=EARLIER(TableDEMO[LastSaturday])))
Now the table looks like this :
Best Regards,
Community Support Team _Isabella
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@eshanpacheriwal
Following measure should return the total sales up to last Saturday, adjust it to suit your data model:
Sales Till Last Sat =
VAR __LAST_SAT = calculate( maxx( FILTER ( all('Date'[Date]) , WEEKDAY ('Date'[Date]) = 5) , 'Date'[Date]))
VAR __DATE_RANGE = FILTER ( ALL( 'Date'[Date]), 'Date'[Date] <= __LAST_SAT )
VAR __RESULT = CALCULATE ( [Sales Amount] , __DATE_RANGE)
RETURN
__RESULT
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
https://community.powerbi.com/t5/Power-Query/Last-Saturday-of-the-Month/td-p/2008520
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |