cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

how to get values for non additive measure for each date level

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

 

 

 

 

 

 

2 REPLIES 2
Super User IV
Super User IV

@YassineERRASFY ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors