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.
HI all,
i am trying to create a DAX for a running total of the last 3 years but i seem to get a syntax error for either Month and Year.
Formula used:
Hello @Anonymous ,
Create a separate table, can be DAX one.
DateT = values(Table_owssvr[Quote out date].[Date])
Relate DateT to Table_owssvr[Quote out date]
Then you should be able to use below formula
Measure =
//Calculate Range to be Aggregated
//Start off by taking latest date in selection this is also the day when calculation should end
var vLDate = max(DateT[Date])
//Get Start Date as start of year 3 years ago
var vStartDate = DATE((Year(vLDate)-3),1,1)
Return // Now to main thing
CALCULATE(
Table_owssvr[Hitrate]
,
All(DateT) // Turn off any filtering on Your calendar table
,
DateT[Date] >=vStartDate // Fix calculation to sum always from Period Start
,
DateT[Date] <=vLDate // Fix calculation to end sum on last day (selected one in chart)
)
I have the column created and merged, but when i try to use the formula it doesnt work.
I have just created the Table as a column in the Excel which contains all the data.
But when i try this code, it gives the following error:
Too few arguments were passed to the DATE function. The minimum argument count for the function is 3.
Hitrate 36M =
//Calculate Range to be Aggregated
//Start off by taking latest date in selection this is also the day when calculation should end
var vLDate = max(Table_owssvr[DateT])
//Get Start Date as start of year 3 years ago
var vStartDate = DATE((Year(vLDate)-3),1,1)
Return // Now to main thing
CALCULATE(
Table_owssvr[Hitrate]
,
All(Table_owssvr[DateT]) // Turn off any filtering on Your calendar table
,
Table_owssvr[DateT].[Date] >=vStartDate // Fix calculation to sum always from Period Start
,
Table_owssvr[DateT].[Date] <=vLDate // Fix calculation to end sum on last day (selected one in chart)
)
I am still stuck on this issue.
Could you tell me how i can link the column to my data?
For some reason that doesnt work for me.
I am getting the error that it cant find the table.
Hi @Anonymous
Could you please provide some more details? How those the data model look like?
With this error table you are reffering to in the measure either does not exist, or it is not related within the model.
Hi @Anonymous
Would it be possible that you need to switch "," separator to ";" to separate arguments in Excel?
Measure is based on data model where we use DateT[Date] as an Axis. This is the table where we are turning off the filters so DateT needs to be related to "Table_owssvr".
Formula will not work when everything is kept in one place. Principle is to have an actual data model to work with (where dimensions and facts are separated).
In excel we use ; indeed but the Power BI uses ,
changed it into ; but still get error (different one)
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 |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
46 | |
28 | |
25 | |
18 | |
17 |