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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SHenryy
Frequent Visitor

Sum of X Days - X days should be a variable

Hello,

 

i have the following problem. 

SHenryy_0-1682488345310.png

 

I want to have to SUM of the last 5 days from "Num1". Including the current day

 

"SUM1 LAST 5" ist the result, which i calculated in excel...but i need this one in power bi...

 

I used the following code for "SUM1 false", but it only sums up everything:

Circulation = CALCULATE(SUM('Tabelle1'[Menge]),FILTER('Tabelle1','Tabelle1'[Tag]<=EARLIER('Tabelle1'[Tag]))) 
 
Somehow i cant set up the filter only for the last 5 days...
 
Any ideas, how to solve this problem?
2 ACCEPTED SOLUTIONS

I've tried it and i'm getting the following error:

"A ring dependency was detected: Table1[Previous5DaySum], Table1[Sum Running Total], Table1[Previous5DaySum]."

 

This is the code:

Previous5DaySum =
VAR CurrentDate = LASTDATE('Tabelle1'[Tag])
RETURN
CALCULATE(
    SUM('Tabelle1'[Menge]),
    DATESBETWEEN('Tabelle1'[Tag], CurrentDate - 'Tabelle1'[LUF ], CurrentDate - 1)
)

View solution in original post

@SHenryy 

Do you want something like this for last 5 days? Please confirm.

rajulshah_0-1682491950172.png

 

View solution in original post

14 REPLIES 14
SHenryy
Frequent Visitor

Pardon Sir - i've added you the table. Is this ok?

TagMengeCirculation Circulation false N Days
02.01.20231651651655
03.01.20235206856855
04.01.2023515120012005
05.01.2023510171017105
06.01.2023480219021905
07.01.20231345337035355
08.01.20231315416548505
09.01.20231275492561255
10.01.20231235565073605
11.01.20231300647086605
12.01.20231315644099755
13.01.202312906415112655
14.01.20237505890120155
15.01.202313055960133205
16.01.202313105970146305
17.01.202313506005159805
18.01.202313306045173105
19.01.202310056300183155
20.01.202312056200195205
21.01.202313006190208205
22.01.202310105850218305
23.01.202312305750230605
24.01.202314056150244655
25.01.202314006345258655
26.01.202313406385272055
27.01.202310106385282155
28.01.202312306385294455
29.01.202314056385308505
30.01.202314006385322505
31.01.202313406385335905
01.02.202314056780349955
02.02.202314006950363955
03.02.202313406885377355
rajulshah
Super User
Super User

@SHenryy 

You can create What-If parameters for the dynamic value of Days (i.e. X).
Please see this link.

 

Then you can create the following DAX measure:

Previous5DaySum =
VAR CurrentDate = LASTDATE(table[DATE])
RETURN
CALCULATE(
    SUM(table[NUM1]),
    DATESBETWEEN(table[date], CurrentDate - [Parameter that you created], CurrentDate - 1)
)

 

Please let me know if this didn't work.

I've tried it and i'm getting the following error:

"A ring dependency was detected: Table1[Previous5DaySum], Table1[Sum Running Total], Table1[Previous5DaySum]."

 

This is the code:

Previous5DaySum =
VAR CurrentDate = LASTDATE('Tabelle1'[Tag])
RETURN
CALCULATE(
    SUM('Tabelle1'[Menge]),
    DATESBETWEEN('Tabelle1'[Tag], CurrentDate - 'Tabelle1'[LUF ], CurrentDate - 1)
)

@SHenryy What is 'Table1[Sum Running Total]'? Maybe that could be the reason.

This is an old collumn...which i dont use anymore? should i delete it?

i deleted all unnessary collumns. 

But as a result my "Previous5DaySum" is now empty

@SHenryy 

Do you want something like this for last 5 days? Please confirm.

rajulshah_0-1682491950172.png

 

True - but which kind of code did you use? 

Yes, but it should already start in 2.Jan 

@SHenryy , but it doesn't have any data before that, so how would it give any value?

@SHenryy 

I created the following DAX measure:

Previous5DaySum = 
VAR CurrentDate = LASTDATE('Tabelle1'[Tag])
RETURN
CALCULATE(
    SUM('Tabelle1'[Menge]),
    DATESBETWEEN('Tabelle1'[Tag], CurrentDate - 5, CurrentDate - 1)
)

pardon sir, how did you created this table?

@SHenryy 
Let me try it in the Power BI Desktop first.

danextian
Super User
Super User

Hi @SHenryy ,

 

Please post a sample data that one can easily copy-paste (not an image) and your expected result. Also I don't see Menge and Tag columns in your screenshot.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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