Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Running total 36 month not working

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:

Hitrate 36 Month Total = CALCULATE(Table_owssvr[Hitrate],DATESINPERIOD(Table_owssvr[Quote out date].[Date],LASTDATE(Table_owssvr[Quote out date].[Date]),-3,YEAR))
 
Can someone help me out so my formula works?
 
Thank you in advance.
7 REPLIES 7
Anonymous
Not applicable

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)
)

 

 

Anonymous
Not applicable

I have the column created and merged, but when i try to use the formula it doesnt work.

Anonymous
Not applicable

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)
)
Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

 

 

Anonymous
Not applicable

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).

 

Anonymous
Not applicable

In excel we use ; indeed but the Power BI uses ,

changed it into ; but still get error (different one)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors