Forum Discussion

jhoyabel's avatar
jhoyabel
Regular Visitor
3 years ago

Dax Measure with Calendar code

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 ReferenceSet Up Complete Date
1053702/01/2021
1054226/04/2021
1054325/03/2022
1056826/05/2022
1217631/07/2023
1217715/08/2023
Need Help
Show and Tell
Tutorial Requests

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      jhoyabel
      Regular Visitor
      3 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      jhoyabel
      Regular Visitor
      3 years ago

      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