Sophisticated Dashboard requirements - need help

bartek_pepper , if you need Date range more than selected, you need a disconnected Date table either in the  slicer or the Axis(Column of the Matrix) 

You need to measure like below 

CALCULATE([net New], KEEPFILTERS(TOPN(5, ALLSELECTED('Item'[Brand]),[Net], DESC)))

Here one net is dependent on slicer value and another net on axis value. The measure will be decided based on the approach we take 

I have discussed the approach here 

https://youtu.be/7dPrPk6LPYU 

Hi bartek_pepper,

here my insights for each request:

There is a matrix that shows names as rows, YearMonth as columns, Sum of Value as Values.

• Based on YearMonth slicer, select only YearMonths backwards from the selected one. Ex. If I choose March, I would like to see January-March, if October, January-October.

FB You need a disconnected table showing the year-months:
Modeling -> New Table

Selection=

ALL ( 'Date[Year-Month] )

Then the measure will have the code

Calculation=
VAR SelectedMonth = SELECTEDVALUE ( Selection[Year-Month] )

RETURN
SUMX ( 
     VALUES ( 'Date[Year-Month] ),
     IF ( 
      'Date[Year-Month] <= SelectedMonth,

      SUM ( Table[Value] )

)
                

supposing 'Date' is your dates table and Table[Value] is the column to aggregate

• Select top 15 names based on the value from the selected month. Ex. I choose March, I get 15 top names based on the sum value for March – still have January-March data as in the above requirement.

FB where and how do you want to see this list of Customers? In a string on a Card, or ?
CONCATENATEX

TOPN ( 
    15,
    SUMMARIZE ( VALUES ( Customer), Customer[Customer key ), Customer[CustomerName] ),
    SUM ( Table[Value] )
),
Customer[Customer key] & " " & Customer[CustomerName],
" / "
) 

• Modify colors of cells based on values from months, green color when value increases month-to-month and red one when values decrease ( in the static measure there a gray color as “else” but it is not relevant now).
  
  FB I suggest Conditional Formatting based on this measure

         Delta MoM = 
         [Measure] - CALCULATE ( SUM ( Table[Value] ), DATEADD ( 'Date'[Date], -1, MONTH ) )

The ideal thing would be to have a Measure, say Calculation = SUM ( Table[Value] ) to avoid spreading the same code everywhere so you can reference to it in the above code instaed of refereing to SUM ( Table[Value] )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Sincerely,

Hi bartek_pepper ,

Thank you for reaching out to the Microsoft Community Forum.

Hi amitchandak  and FBergamaschi , Thank you for your prompt responses.

Hi bartek_pepper could you please try the proposed solutions shared by amitchandak  and FBergamaschi  ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh