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
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.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 359 members 3,352 guests
Please welcome our newest community members: