Dynamic sort by column based on fiscal year

[Anonymous]

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.

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

Regards

[Anonymous]

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

[bsas]

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.