get semi-additive measure value for each date level

[Anonymous]

Hello, I hope you are doing well!

I have posted this before but I didn't get an answer; I'm blocked and I need help.

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 have other attributs like duty cycle ( creation, modification, termination)  production entity...

I need also to visualise the current orders measure by these descriptive fields ( the measure can be aggregated by these fields)

I dont have a separate date table; I have year , semester, month and week period columns within the 360ST table.

I created flags in the 360ST table that store the  last week of each  month and last week of the year.

I have created the current orders measure like this :

var Total current orders = sum('360ST'[order number])

VAR year current orders = CALCULATE (

SUM ( '360ST'[order number])
,'360ST'[last week of the year] = 1
, ALLEXCEPT('360ST','360ST'[year],'360ST'[last week of the year] )
)

VAR Month current orders =

CALCULATE (

SUM ( '360ST'[order number])
, '360ST'[last week of month] = 1
, ALLEXCEPT('360ST','360ST'[Month], '360ST'[last week of month])
)

VAR yearIsSelected = ISFILTERED( '360ST'[year])

VAR MonthIsSelected = AND ( ISFILTERED ( '360ST'[Month]), NOT( ISFILTERED ('360ST'[year]) ) )

RETURN

SWITCH (

TRUE(),
yearIsSelected,year current orders,
MonthIsSelected, Month current orders,
Total current orders
)

Here is the input :

Year	Month	Week	order number	last week of month	last weeek of Year
2020	202010	202040	233
2020	202010	202041	101
2020	202010	202042	321
2020	202010	202043	1000
2020	202010	202044	500	1
2020	202011	202045	233
2020	202011	202046	101
2020	202011	202047	321
2020	202011	202048	1000	1
2020	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

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

the problem with the measure that I created is :

 >>when I select month and year both, the measure get year value not month value

>> when i selected month and week both, the measure get month value not week value

It seems isFiltered function doesn't work

thanks

[littlemojopuppy]

Can you provide a sample PBIX file?