x-axis alias on cumulative measure

[Anonymous]

Hey Everyone,

I have a cumulative measure which looks like this:

CV TFYTD =
     CALCULATE(
          SUM(Opportunities[CV TFY]),
          FILTER(
               ALLSELECTED('Date'[FYMonth]),
               ISONORAFTER('Date'[FYMonth], MAX('Date'[FYMonth]), DESC)
               )
         )

FYMonth is a calculated column in a linked date table which returns text of "01 - Jul", "02 - Aug", "03 - Sept", so that the cumulation calculates on a financial year. It's intentionally ignorant of the year because I plot a [last FY] measure against a [this FY] measure on the same axis. This actually works fine so far, but my users get confused by the prefixes. I want to take away the "01", "02", "03" part of the display but that messes with how my measure is working.

I tried making a new column and using the sort by, but this doesnt work for cumulative measures which rely on the logical order of the field you filter on.

Does anyone know how to alias an axis which is used by a cumulative measure? or maybe it's in how I've created my actual meaure up top thats setting me up for failure - any guidance would be awesome 🙂

[tex628]

Is it not possible for you to create another calculated column converting the 01 - Jan etc. to "January" and so on. Then use this column for the axis while still running the value on the previous measure?

/ Johannes

[Anonymous]

Thanks for the reply tex628 🙂

Unfortunately, doing this results in the measure returning the values not cumulated at all, as if my measure was simply:

CV TFYTD = Opportunities[CV TFY]

It seems that the referenced field in the cumulation measure has to be the one thats in the visual?

This might be different if I did the cumulation as a calculated column instead of a measure? I'm still getting my head around when to use measures verse CC 🙂 Let me try this too 🙂

[tex628]

May i ask what type of visualisation you are using for this?

[Anonymous]

It is the default Line Chart, with FYMonth/Month on the x-axis and the measures stacked in the values space.

I also have put it in a default table visual to look at the figures and make sure they are appearing correctly.

[tex628]

I tried to recreate the situation. Is this what you are aiming for in terms of visuals?

[Anonymous]

Hi tex628 🙂

Yes, but a cumulative measure, so that February displays Jan+Feb, and March displays Jen+Feb+March, and also starting in July (financial year).

It's the cumulative part that i cant seem to get going with the nice naming conventions 🙂

[tex628 · Accepted solution]

Alright, i did some things. pictures at the bottom.

First of all, I created a calculated column with cumulative month values. 

Then i changed the CV TFYTD measure.

Ignore the names of tables and columns*

Final resultCalc columnChanged allselected to affect entire table.

Column = IF(ArticleDeclaration[Custom]<2;"Jan";
    IF(ArticleDeclaration[Custom]<3;"Jan-Feb";
        IF(ArticleDeclaration[Custom]<4;"Jan-Mar";
             IF(ArticleDeclaration[Custom]<5;"Jan-Apr";
                 IF(ArticleDeclaration[Custom]<6;"Jan-May";
                     IF(ArticleDeclaration[Custom]<7;"Jan-Jun";
                         IF(ArticleDeclaration[Custom]<8;"Jan-Jul";
                             IF(ArticleDeclaration[Custom]<9;"Jan-Aug";
                                 IF(ArticleDeclaration[Custom]<10;"Jan-Sep";
                                     IF(ArticleDeclaration[Custom]<11;"Jan-Oct";
                                         IF(ArticleDeclaration[Custom]<12;"Jan-Nov";
                                            "Jan-Dec"
        )))))))))))

[Anonymous]

Holy Quackamoley!

It took me quite a while to digest this.

I was thrown off a little since you put [custom] as the cumulative sum as well, when of course this should be whatever I need it to be.

Anyway I just put it all together and it does indeed work! Thank you so much tex!!! <3<3<3

[johnmu]

So does that meet your requirement?

Also, you may want to use the SWITCH function instead of nested IF statements. See this blog post to explain why/how

https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/

[tex628]

I did not know of the Switch function! Will use it in the future 🙂

[Anonymous]

Thanks johnmu,

Yes that was the other bit that made it take a while to digest.

tex described it as 'cumulative month values' which makes me think this is different to just switching the numbers to text values like you would with a SWITCH, but I'm going to need to spend some time understanding this code before I can say I understand whats going on.