(Another) Sort by another column problem

[Anonymous]

Trying to sort a column in my custom date table (a csv file) via a calculated column in the same table but am seeing an error. The calculated column does not reference the column I wish to sort by. Here's the DAX for the calculated column:

PeriodOffset = (Dates[Period]+(Dates[FiscalYear]*13))-
                (CALCULATE(VALUES(Dates[Period]),
                            Dates[Date]=TODAY())+
                (CALCULATE(VALUES(Dates[FiscalYear]),
                    Dates[Date]=TODAY())*13
                    ))

My date table has every date from 2003/4 to 2034/35, along with custom period numbers, calendar and fiscal years etc. The column I am trying to sort is called PeriodFiscalYear. Each value in that column has only one entry in the PeriodOffset column so it's not that.

The weird thing is, I have had this working in a previous report. In this instance, I was simply trying to recreate the functionality but it won't do it. Even stranger, if I create the PeriodFiscalYear column as a calculated column (currently it's hard-coded in the csv file), it works! So I have a sort-of work around, I would just like to understand what is going on.

Thanks

[v-shex-msft]

HI @Anonymous ,

You can also take a look at the following blog about creating a custom sort order table to achieve customer sort order:

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

[ChrisMendoza]

@Anonymous -

What is the error you received?

[Anonymous]

[ChrisMendoza]

@Anonymous -

You've identified this table as a 'Mark as Date Table' correct? Can you provide a sample?

[Anonymous]

No, I haven't marked it as a date table. I don't need to use the time intelligence fucntions as my calendar has custom periods.  I'd be happy to provide the csv but I don't have anywhere I can upload it that I can access as work.

[ChrisMendoza]

@Anonymous -

Are they not custom periods based off of sequential dates? Those dates become the sort order. Time Intelligence functions do not need to be utilized just by marking as a date table.

I built a custom period calendar table, marked as date table, and have had no issues with sorting. Possibly it is just the implementation you've chosen that is causing intermittent successes.

[Anonymous]

@ChrisMendoza 

Are they not custom periods based off of sequential dates?

Yes, but the periods change slightly each financial year. For example, 29th April can be in Period 1 or Period 2. The logic for this is implemented.

Those dates become the sort order. Time Intelligence functions do not need to be utilized just by marking as a date table.

I wish to sort by Period/Fiscal Year for visuals. If I don't do this, the x-axis of a chart showing the last 13 periods of data would put period 13, 2018/19 after period 1, 2019/20. Hope that makes sense.

I can certainly try marking it as a date table, I'm just not sure why this would matter!

[ChrisMendoza]

@Anonymous -

Maybe take a look at how I implemented my custom periods at http://cjmendoza.yourweb.csuchico.edu/custom-calendar/create-a-custom-period-calendar-in-power-query/. Seems like your need is similar.

[Anonymous]

@ChrisMendoza 

I've had a look at your post. Very similar to what I've done, except I did it in Excel then imported into Power BI. I think my PeriodOffset column is similar to your index column, except mine is dynamic; i.e. it's recalculated everytime the model is loaded. This means in time intelligence functions, I don't have to find the current period number each time: I know it's PeriodOffset = 0.

I'm still confused as to why my method is not sortable. Perhaps the way I am doing it isn't the best. I did try generating the column within the query, but I couldn't get the list functions to accept a table column as an argument.

[ChrisMendoza]

@Anonymous -

I would be interested in seeing your file. Hope you find your solution, best of luck.

[Anonymous · Accepted solution]

I believe I've solved this problem. It was caused by a relationship between my date table and a fact table. The relationship was between a calculated column in the latter that looked up a value from a column in the former. Power BI allowed it, but it seemed to affect the behaviour of reports built using the dataset.

Removing the relationship means I can perform the sort I need.

To recreate the relationship, I've calculated a table in the query that include only the columns I need and inserted this between to the two tables in my model.

[Anonymous]

Cheers Chris, I will. I'm off home now (in the UK) but will post back tomorrow.