Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
PBI Newbie here! Need help on how to formulate the dax code on how to measure with coded calendar in the Table data from Jan 1 2021 to Aug 15, 2023. Thank you for the help
Case:
Dashboard Dynamic Filter Date: Current Filter at 2023 until July 31, 2023
MeasureDax needed: Count all the data from Jan 1 2021 until july 31, 2023 ( or depends on what the selected filter but it counts starting from minimum date available in the data (e.a Jan 1 2021)
Outcome: 5
Data:
Vacancy Reference | Set Up Complete Date |
10537 | 02/01/2021 |
10542 | 26/04/2021 |
10543 | 25/03/2022 |
10568 | 26/05/2022 |
12176 | 31/07/2023 |
12177 | 15/08/2023 |
Hi @jhoyabel ,
Please refer to my pbix file to see if it helps you.
Create a new table with date column. Then put the date column into the slicer. (Why create a new table: it can give you more control over the filtering process. By creating a separate table, you can customize the slicer to include only the values that you want to display, and you can also add additional columns to the table to provide more context for the values in the slicer. )
Then create a measure.
Measure =
VAR _1 =
SELECTEDVALUE ( 'Table 2'[Set Up Complete Date] )
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALL ( 'Table' ), 'Table'[Set Up Complete Date] <= _1 )
)
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! I already have an existing Calender Slicer which has the same date of the set up complete date but my current table has no connection on the calendar.
I want to create a measure where the calendar is coded which connects to the calendar slicer where it counts all data in the table and the slicer serves as "As of date" so
Calendar Slicer: selected at all date/months in 2023 until Aug 15
Output : 6
And when date slicer changes to 2023 until Jul 31
Output : 5
@jhoyabel , you can use datesytd or totalytd for that
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
How do you make data output dynamic once the filter date is set to a specific end date
iex. Filter date - 2023 until Aug 15
Outcome - 6
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
104 | |
77 | |
35 | |
35 |
User | Count |
---|---|
158 | |
103 | |
71 | |
65 | |
53 |