Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need to calculate the last 12 months of sales per material number, but I need to calculate it based on today and then 12 months back. This means that it has to be calendar/time dimension independent.
I tried to do something like:
Sales12M = CALCULATE (
[Sales QTY];
DATESBETWEEN (
Calendar[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( Today() ) );
Today()
)
)
But that doesn't work as I get the following error: The first argument to 'SAMEPERIODLASTYEAR' must specify a column.
Furthermore DATESBETWEEN in above points to the Calendar table, but that doesn't make sense in this case, so perhaps I am not on the right path at all.
The reason for why I need to use the calculation calendar independent is that I need to see the sales prices per material number in the last two years, but I need the quantity from the last 12 months to make a kind for weighted price.
As a table I would like the following:
Period Material Sales Price Sales12M Weighted Price
2016-01 A 100 2 200
2016-02 A 110 2 220
...
2017-01 A 200 2 400
Hi both,
I have tried in my Calendar table to add the column "Today = Today()" and then used that one in the formula as follows:
Sales12M = CALCULATE (
[Sales QTY];
DATESBETWEEN (
Calendar[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( 'Calendar'[Today]) );
Today()
)
)
It does not return any errors, but the result is that I get the Sales QTY for all periods, not only the last 12 months. Other filters are reflected though.
I then tried to move the Today column to my 'Movments' table and made the following formula:
Sales12M = CALCULATE(
[Sales QTY];
FILTER(
ALL('Movements');
Movements[Date]>=DATEADD(Movements[Today];-1;YEAR) && Movements[Material] = Max(Movements[Material])))
That however returns all periods Sales QTY and also does not reflect filters from other tables.
I think the issue is that I have a calendar table and a material number table that is connected to the 'Movements' table where the Sales QTY is comming from.
Hi @Morten_DK,
Are you able to achieve your requirement now? If not, would you please share sample .pbix about Movements and Calendar table for our analysis?
Best Regards,
Qiuyun Yu
Hi @Morten_DK,
In your scenario, you can create a calculated column Today = TODAY(). Then create a measure like this:
Sales12M = CALCULATE(SUM(Table1[Sales QTY]),FILTER(ALL(Table1),'Table1'[Period]>=DATEADD('Table1'[Today],12,MONTH) && 'Table1'[Material]=MAX('Table1'[Material])))
Best Regards,
Qiuyun Yu
Have you tried creating a calculated column using the TODAY() function and using that in your SAMEPERIODLASTYEAR function?
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |