Forum Discussion

DominicK's avatar
DominicK
Helper I
5 years ago
Solved

Count Values Between Two Dates

I have created the below column chart which is correct however the DAX code I used to create the columns is far too long.   I want to show the values of Asset Class between Sep 19 to Feb 20 and the...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

@DominicK, you can try as

calculate(countA(Sheet1[Asset Class]), filter(Sheet1,Sheet1[Date] > date(2019,09,01) && Sheet1[Date] < date(2020,02,29)))

You can try this with the date table

calculate(countA(Sheet1[Asset Class]), DATESBETWEEN(DAte[Data], date(2019,09,01), date(2020,02,29)))

You can use date segmentation and this will work -countA(Sheet1[Asset Class])

To get the best out of the time intelligence function. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of your events. Recommended:
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my Time Intelligence webinar can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


We appreciate your congratulations.

  • DominicK's avatar
    DominicK
    Helper I
    5 years ago

    amitchandak 

    Thank You!

     

    I will be looking at automating this report.

     

    Do you have any advice on how to create a measure that shows a rolling 6 months?

     

    Next quarter I will want to run this again but the dates will be different( December 2019 - June 2020 and July 2020 - December 2020)

     

     

    Thanks!