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,
1st post, so I hope I will not mess up 😉
I got the following tables:
The calendar periods I create with...
Calender Periods =
var _last30days = ADDCOLUMNS(CALCULATETABLE('Calender', DATESBETWEEN('Calender'[Date], TODAY() - 31, TODAY() -1)), "Range", "last 30 days")
var _last07days = ADDCOLUMNS(CALCULATETABLE('Calender', DATESBETWEEN('Calender'[Date], TODAY() - 08, TODAY() -1)), "Range", "last 07 days")
var _yesterday = ADDCOLUMNS(CALCULATETABLE('Calender', DATESBETWEEN('Calender'[Date], TODAY() - 01, TODAY() - 01)), "Range", "yesterday")
var _next07days = ADDCOLUMNS(CALCULATETABLE('Calender', DATESBETWEEN('Calender'[Date], TODAY() + 1, TODAY() + 08)), "Range", "next 07 days")
var _next30days = ADDCOLUMNS(CALCULATETABLE('Calender', DATESBETWEEN('Calender'[Date], TODAY() + 1, TODAY() + 31)), "Range", "next 30 days")
return
UNION(_last30days, _last07days, _next07days, _next30days, _yesterday)
...and connected like this:
I want to compare sales of last 7 days (or last 30days) with the sales one week before "last 7days".
So I created measure:
Sales amount -7 days =
CALCULATE(SUM(Sales[Sales amount]),
DATEADD(Calender[Date], -7, DAY)
)
When I now use the column "Range" from Calender Periods table as a slicer, the dates from calender tables get cut to only last 7 days.
As far so good.
But this is, why my measure "Sales amount -7 days" does not work anymore.
How do I "free" the date filter für the -7 days calculation?
Attached pbix file:
Thx for your help.
dEllE
Solved! Go to Solution.
Hi, @dEllE ;
You could modify the measure such as:
VAT Base Amount w Ship Dateadd -7 days Msr =
CALCULATE(SUM(sales[Sales amount]), FILTER(ALL('Calender Periods'),DATEDIFF([Date],MAX('Calender'[Date]),DAY)=7))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @dEllE ;
You could modify the measure such as:
VAT Base Amount w Ship Dateadd -7 days Msr =
CALCULATE(SUM(sales[Sales amount]), FILTER(ALL('Calender Periods'),DATEDIFF([Date],MAX('Calender'[Date]),DAY)=7))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@dEllE , Measure
based on selected date
Rolling 7 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
based on today
Rolling 7 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today() ,-7,DAY))
& before that
Rolling 7 b 7= CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]) -8 ,-7,DAY))
based on today
Rolling 7 b 7= CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today()-8 ,-7,DAY))
Hi Amit,
Rolling 7 b 7 does not work.
When calculating like this, the amount per day is not correct anymore..
Attached please find the pbix file.
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 |
---|---|
115 | |
101 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |