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.
Hello guys, i'm doing some manipulations with dates
CAn u help with it because i cant find a solution i need.
On my page i will have to filter tables : year and month number
I want to count how many rows i have that satisfy my condition.
Condition is about : if i choose a date ( year and month), how many rows i have for which my date is in interval of date of entry and date of depart. ( two columns of dates that are in my principal data table). IT'is like to be able to choose a date (year and month) and then compare it to my date of entry and date of depart. If a choosen date is otsuide or inside the interval.
I decided to start with a simple : i want to calculate how many rows i have for which YEAR of my date of entry is less then the YEAR selected in a filter.
Thanks for any help
Hope it was clear. I send you some screens to let it be more precise.
Solved! Go to Solution.
@Anonymous , That should come from date table. Based on allselected you can always get the year
measure =
var _max = maxx(allselected(Date),Date[Year])
return
calculate(countrows(Table), filter(all(Date),Date[Year]<=_max))
or
measure =
var _max = minx(allselected(Date),Date[Year])
return
calculate(countrows(Table), filter(all(Date),Date[Year]<=_max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
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 webinar on Time Intelligence 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
Appreciate your Kudos.
@Anonymous , That should come from date table. Based on allselected you can always get the year
measure =
var _max = maxx(allselected(Date),Date[Year])
return
calculate(countrows(Table), filter(all(Date),Date[Year]<=_max))
or
measure =
var _max = minx(allselected(Date),Date[Year])
return
calculate(countrows(Table), filter(all(Date),Date[Year]<=_max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
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 webinar on Time Intelligence 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
Appreciate your Kudos.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |