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 am trying to show a lead funnel within a matrix. All fields except for date are measures. Quote goal is a measure based of win goal, but it takes about 2 months for a quote to turn into a win. So I need the quote goals to align back 2 months. Any suggestions on how ot do this?
Solved! Go to Solution.
Hi @cthurston
If [Measure] values does not equal to the return scalar value of CALCULATE ( [Mesure], DATEADD ( Table[Date], 0, MONTH ) ) then most likely the issue is with date table . DATEADD only works with contiguous selection of dates.
To test this:
1) You can create a new table and run just one dax function CALENDARAUTO() this will create a new date table with one field of consecutive dates .
2) Create new relationship between newly created date table and your sales (fact) table
3) Use this table as your first argument "Table[Date]" in DATEADD.
If DATEADD starts to then you need to tweak your original date dimension.
Thanks, Nick
The general format for calculateing measures in different time period is to use DATEADD see below for an example to calculate a rest one month in the past
Measure Last Month = CALCULATE([Measure],DATEADD(DateTable[Date],-1,Month))
Hello Thomas I am not getting the desired result. Am I correct in thinking at 0 the value should be the same value? I'm getting a bunch of 0s and a few innacurrate numbers.
The value will not be the same at 0. If you want that, you will have to add it with conditional logic - if statements , etc..
N -
So maybe I'm confused as to what the dateAdd statement does. Since CALCULATE([Mesure],DATEADD(Table[Date],0,MONTH) dosen't just give me the [Measure] value what is it doing?
Hi @cthurston
If [Measure] values does not equal to the return scalar value of CALCULATE ( [Mesure], DATEADD ( Table[Date], 0, MONTH ) ) then most likely the issue is with date table . DATEADD only works with contiguous selection of dates.
To test this:
1) You can create a new table and run just one dax function CALENDARAUTO() this will create a new date table with one field of consecutive dates .
2) Create new relationship between newly created date table and your sales (fact) table
3) Use this table as your first argument "Table[Date]" in DATEADD.
If DATEADD starts to then you need to tweak your original date dimension.
Thanks, Nick
Perfect this worked thank you!
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 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |