Dax Measure with Calendar code

[jhoyabel]

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

[v-rongtiep-msft]

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.

[jhoyabel]

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

[amitchandak]

@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

[jhoyabel]

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