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
