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
v-ljerr-msft Super Contributor
Super Contributor

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.

v-ljerr-msft Super Contributor
Super Contributor

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 394 members 3,949 guests
Please welcome our newest community members: