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

Dynamic sort by column based on fiscal year

Hi 

 

I have 4 companies with 2 different fiscal years between them.  I have created a report which filters on the company field (using report level filter).  The order of the months on trend graphs will need to change dependant on the fiscal year end of the company selected.  

 

I have created a measure which extracts the company selected (Measure1).  I have created a new column in my month order table (Month Order), using this measure in an if statement to determine the order of the months (based on fiscal year end of the company selected).  However, the value of the measure is not pulled through to the month order table.

 

Measure1 = if(CONTAINS('Consolidated Group','Consolidated Group'[Company],"COMPANY 1"),1,2)

Month Order = if([Measure1]=1,'Month Order Table'[Month Order Base]+1,'Month Order Table'[Month Order Base])

 

If COMPANY 2 is selected, the Measure value in the Month Order formula does not change.  The Measure appears to remain as 1, no matter what is company is selected.  (However, if I create a card visual and pull in the Measure, it shows the change in value when a different company is selected. )

 

Would greatly appreciate assistance.  Many thanks.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: Dynamic sort by column based on fiscal year

Hi @karen,

 

Here is a similar thread in which a workaround is mentioned. Could you go to check if it helps in your scenario? Smiley Happy

 

Regards

View solution in original post

3 REPLIES 3
Highlighted
bsas Member
Member

Re: Dynamic sort by column based on fiscal year

Hi,

 

I had similar problem when I needed to show different total APM and APM when choosing one of the territory. For this I used:

 

APM when territory selected:
APM_ter = IF(ISFILTERED(Terriroty[Territory]) && DISTINCTCOUNT(Terriroty[Territory]) =1, SUM('Territory Goals'[FactAPM]), BLANK())
APM when all are selected:
total APM = IF(ISFILTERED(Terriroty[Territory]) && DISTINCTCOUNT(Terriroty[Territory]) =2, (SUM('Territory Goals'[TotalFactApm])/2), BLANK())

1 and 2 this is state of slicer. 1 - selected one of the terrritory, 2 - all selected.

 

Then apply this measures as values in your chart. You can combine it into 1 if needed. Hope this will help you to find out how to change your measures.

Microsoft v-ljerr-msft
Microsoft

Re: Dynamic sort by column based on fiscal year

Hi @karen,

 

Here is a similar thread in which a workaround is mentioned. Could you go to check if it helps in your scenario? Smiley Happy

 

Regards

View solution in original post

karen Regular Visitor
Regular Visitor

Re: Dynamic sort by column based on fiscal year

Thank you so much, I used some of these suggestions to solve my problem

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors