how to switch year/month from right to left respectively in clustered chart

[majdkaid22]

hi guys,

Am trying to have the visualization going from rightto left (current year to previouis year) on monthly basis respectively 

Right now Jan 2016 is presented on the right and right after that comes May/Jun...etc to the end of 2015

I did the date sorting Ascending/Descending, as I read few comments here and there that it could help, but it's not working for me.

An suggestions? 

[fbrossard · Accepted solution]

 Hi @majdkaid22,

Just add two calculated columns in your model, [year month code] and [year month name] :

[Year Month] = INT(FORMAT([Date],"YYYYMM"))
[Year Month Name] = FORMAT([Date],"YYYY, MMMM")

Sort the [Year Month Name] by the column [Year month] ans use it as your axis.

[majdkaid22]

it has indeed worked out @fbrossard! 

many thanks mate

[fbrossard]

Cool.
Pleased to help you.

[majdkaid22]

@fbrossard As you are being very generous with me, could I ask one more thing related to dates?

I have used the Datesteam from Azure, and uploaded it to my report, in order to use it in a Slicer. I have created one active relationship between my deposit report (Deposit[Date]) and the calender (DateStream[DateKey]). 

It seems the above relation is deactivating the Date Hierachy for (Deposit[Date]) 

It looks like am doing something either wrong or my columns are not matching

Appreciate your help if the above make sense

Cheers,

M

[greggyb]

Check that Deposit[Date] is a datetime with the time portion set to 00:00:00. Note that data format in the model **DOES NOT** alter the data stored in a field, only its display format.

Create a calculated column in Deposit:

Test =
Deposit[Date]
    = DATE(
           YEAR( Deposit[Date] )
           ,MONTH( Deposit[Date] )
           ,DAY( Deposit[Date] )
      )

If any values are false, you've got times in your Deposit[Date].

[majdkaid22]

 @greggybit indeed had time in it, however I now fixed that, where it only dates. The visualization is working fine as I have created what @fbrossard has suggested, but this time in the Calender. 

Am obviously  not able to create a relationship between Calender and both (Accounts) & (Deposits) at the same time, but however when am connecting the calender to either, I shouldn't be using the Calender as a date Axle for any visualization from the report that has no direct relationships, as am getting incorrect figures.

At the moment am using (Accounts[Date]) as the date Axis when creating any visualization for any data from that source, and using (Deposit[Date]) when it's related to deposit report. 

what I can't understand, why I can't connect the Calender "Datesteam" to both Accounts & Deposits, as my logic is saying it can be done, and I will be using one centralized source of date.

 

[greggyb]

Account filters Deposit. Date filters Deposit. What would happen if you applied conflicting filters on Date and Account? What should be displayed from Deposit? This is not a question you need to answer, because while it may make sense in your case, it doesn't work in the general case, and so the Tabular engine will not allow it. Only one active relationship is allowed between two tables. You have two relationships between Date and Deposit. One runs Date -> Deposit. The other runs Date -> Account -> Deposit.

If you need to use multiple relationships, you need to create measures that take advantage of USERELATIONSHIP():

MeasureFilteredByAnotherRelationship =
CALCULATE(
    <expression>
    ,USERELATIONSHIP( <many table>[<join field>], <one table>[<join field>] )
)

This construction requires that the relationship be defined in the model as inactive.

My question is why do you need to sort a dimension by Date? Typially we'd expect all dimensions to filter the fact. What date is essential in Account?