Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sathvik123
Helper V
Helper V

Show data based on slicer selection

Hello All,

 

I have table where I am having daily data like date,sales,profit and discount.

 

My requirement is I need to show a slicer which have values like daily,weekly,monthly and Quarterly.

If the user selects daily I need to show last 30 days sales,profit and discount.

If the user selects Monthly I need to show last 12 months data.(Here I need to aggrigate data for monthly)

If the user selects Weekly I need to show last 12 Weeks data. (Here I need to aggrigate data for Weekly)

If the user selects Quarterly I need to show last 4 qtrs data. (Here I need to aggrigate data for qtrly)

 

So I created a table in power bi with these values and showing as slicer.

Not sure how to write the dax to calulate these.

 

 

Sample Data :

 

Sathvik123_0-1678883986617.png

 

Could any one please help on this

3 REPLIES 3
amitchandak
Super User
Super User

@Sathvik123 , Have these as the name of Date, Month , week and Qtr column, you want to display on axis

 

rolling 12 =  // Same for month and qtr
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1
BLANK())
return
CALCULATE([net] ,filter(Date, Date[Date] >= _min && Date[Date] <=_min))

 

 

days

=

var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = _max -30
BLANK())
return
CALCULATE([net] ,filter(Date, Date[Date] >= _min && Date[Date] <=_min))

 

 

for week you need use week rank column in date table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

 

Last 12 weeks = CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-11 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

Now use the field parameter on the renamed column of date table and change the measure as suggested in blog

 

selected parameter code is important

Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

 

Hi @amitchandak ,

 

In the below calculation what is meant by [net] ?

 

CALCULATE([net] ,filter(Date, Date[Date] >= _min && Date[Date] <=_min))

@Sathvik123 , here net is my measure. You can use your measure

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.