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,
I want to do a sum of value where my date start 30/06/(YEARS(TODAY())-3 AND end date 30/06/YEAR(TODAY()) but when i try this i have an error message.
Can you help me please ?
Reguards
Solved! Go to Solution.
Hey,
you can uses this DAX statement to create your measure
Measure = var yearToday = YEAR(TODAY()) var yearStart = yearToday - 3 var theDatesTable = DATESBETWEEN('Calendar'[Date], DATE(yearStart, 6, 30),DATE(yearToday, 6, 30)) return CALCULATE( SUM('FactWithDates'[Amount]) ,theDatesTable )
Please be aware that this Measure heavily relies on the common practice to use a separate calendar/date table, where this date table relates to your fact table, please also have a look to at this site where a lot of common date related calculations are explained: daxpatterns.com/time-patterns
Hm, I'm wondering if your start should be 7/1/year(today())-3 (representing the 1st of July 3 years ago), if you want to cover complete 12 months.
Regards
Tom
Hi @AlexGallet01,
Have you tried the solution provided by @TomMartens above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?
If you still have any question on this issue, feel free to post here.
Regards
This should work just adjust to your table and column names:
Measure = calculate(COUNT(PBIDataSource[No.]), filter(ALLSELECTED(PBIDataSource),
PBIDataSource[Date] > date(2017,01,01) &&
PBIDataSource[Date] < date(2017,02,01)
)
)
Regards
Abduvali
Hey,
you can uses this DAX statement to create your measure
Measure = var yearToday = YEAR(TODAY()) var yearStart = yearToday - 3 var theDatesTable = DATESBETWEEN('Calendar'[Date], DATE(yearStart, 6, 30),DATE(yearToday, 6, 30)) return CALCULATE( SUM('FactWithDates'[Amount]) ,theDatesTable )
Please be aware that this Measure heavily relies on the common practice to use a separate calendar/date table, where this date table relates to your fact table, please also have a look to at this site where a lot of common date related calculations are explained: daxpatterns.com/time-patterns
Hm, I'm wondering if your start should be 7/1/year(today())-3 (representing the 1st of July 3 years ago), if you want to cover complete 12 months.
Regards
Tom
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |