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, hope you are doing well !
I have a SSAS tabular model , based only on one table named 360ST, that contains the following measures:
-new orders,
-commissioning ( installation at client site) of new orders
-current orders ( current orders under production); 360ST[order number] is the column that holds the number of orders.
the current orders is a non additive measure:
- its value at year level is the value for the last week in the year
- its value at month level is the value for the last week in that month
- its value at week level is the aggregation by week
I created flags in the 360ST table that store last week of the month and last week of the year.
I have created three intermidiate measures to calculate current orders measure value at each level:
>>year level :
year current orders:=
CALCULATE (
SUM ( '360ST'[order number] ),
FILTER (
'360ST'
,'360ST'[last week of the year] = 1
)
)
>>Month level
Month current orders :=
CALCULATE (
SUM ( '360ST'[order number]),
FILTER (
'360ST',
'360ST'[last week of month] = 1
)
)
>> week level :
current orders at week level is the aggreation of Total current orders := SUM ('360ST'[order number]) by week when the the user is selecting a week.
I have created a global measure for current orders to manage all these cases :
current orders:=
IF ( SELECTEDVALUE ( '360ST'[Year] )
, [year current orders]
, IF (
SELECTEDVALUE( '360ST'[Year]) && SELECTEDVALUE('360ST'[Month])
,[Month current orders]
, IF (
// the structure of 360ST'[Year_Week] is YYYYWW (202044)
SELECTEDVALUE ( '360ST'[Year_Week] )
,[Total current orders]
)
)
)
I have created a power bi dashboard using a liveconnection mode to connect to the ssas tabular model, I have created 3 slicers : Year, Month and Week and a clustered column chart containing a hierarchy ( Year,Month,Week ) on the axis and current orders on the Value field, but it seems that the global measure that I created doesn't work correctly.
does anyone can help please?
Thank you
@YassineERRASFY ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hello @amitchandak , sorry for This late answer, I've been facing problem with mu user @YassineERRASFY
Here is the input :
Year MonthNumber YearMonthNumber YearWeek order number last week of month last weeek of Year
2020 10 202010 202040 233
2020 10 202010 202041 101
2020 10 202010 202042 321
2020 10 202010 202043 1000
2020 10 202010 202044 500 1
2020 11 202011 202045 233
2020 11 202011 202046 101
2020 11 202011 202047 321
2020 11 202011 202048 1000 1
2020 12 202012 202053 2600 1 1
desired output :
Year Month Week current orders by week current orders by month current orders by year
2020 202010 202040 109943 107236 111943
2020 202010 202041 108963 107236 111943
2020 202010 202042 106643 107236 111943
2020 202010 202043 110253 107236 111943
2020 202010 202044 107236 107236 111943
2020 202011 202045 109963 106403 111943
2020 202011 202046 108112 106403 111943
2020 202011 202047 109080 106403 111943
2020 202011 202048 106403 106403 111943
2020 202012 202053 111943 111943 111943
or
year current ddp
2020 111943
year Month current ddp
2020 10 107236
2020 11 106403
2020 12 111943
week 202044 is the last week for november 2020 so the the value for november 2020 is 107236
The value for year 2020 is the value for last week in december 2020 ( week 202053) is 111943
Thanks for helping
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |