Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

conditional column and dax calculations

good afternoon masters   Again I am in the need to come to his knowledge, I happen to be making a report and I have encountered several difficulties, (including lack of creativity) :smileyfrustrate...
Need Help
  • pawel1's avatar
    pawel1
    7 years ago

    Hi,

    1st - your formula for grouping hours would work if you sort the hours from the latest to the earliest:

    rango_horario = SWITCH(TRUE(),
                    hurto_personal[hora] >= TIME(22,00,00),"10 - 12 pm",
                    hurto_personal[hora] >= TIME(20,00,00),"8 - 10 pm",
                    hurto_personal[hora] >= TIME(18,00,00),"6 - 8 pm",
                    hurto_personal[hora] >= TIME(16,00,00),"4 - 6 pm",
                    hurto_personal[hora] >= TIME(14,00,00),"2 - 4 pm",
                    hurto_personal[hora] >= TIME(12,00,00),"12 - 2 pm",
                    hurto_personal[hora] >= TIME(10,00,00),"10 - 12 am",
                    hurto_personal[hora] >= TIME(08,00,00),"8 - 10 am",
                    hurto_personal[hora] >= TIME(06,00,00),"6 - 8 am",
                    hurto_personal[hora] >= TIME(04,00,00),"4 - 6 am",
                    hurto_personal[hora] >= TIME(02,00,00),"2 - 4 am",
                    hurto_personal[hora] >= TIME(12,00,00),"10 - 12 am","validar")
     
    2nd: I assume you need a formula for Compound Annual Growth Rate (CAGR), If so, you can write either a static one (valid for years 2010-2018 only):
     
    CAGR 2018vs2010 = 
var Hurtos2010 = 
    CALCULATE([tot_hurtos], 
        Dates[Year] = 2010)
var Hurtos2018 = 
    CALCULATE([tot_hurtos], 
        Dates[Year] = 2018)
Return
(Hurtos2018/Hurtos2010)^(1/8)-1

    or a dynamic one (for any selected years):

    CAGR dynamic = 
var HurtosFirstYear = 
    CALCULATE([tot_hurtos], 
        FILTER(ALLSELECTED(Dates),Dates[Year] = MIN(Dates[Year])))
var HurtosLastYear = 
    CALCULATE([tot_hurtos], 
        FILTER(ALLSELECTED(Dates),Dates[Year] = MAX(Dates[Year])))
var NumberOfYears = 
    MAX(Dates[Year])-MIN(Dates[Year])
Return
(HurtosLastYear/HurtosFirstYear)^(1/NumberOfYears)-1

    aha, and you need a 'Dates' Table with at least two columns 'Date' and 'Year' to make it work.

    hope it helps, good luck!

    Pawel

pawel1's avatar
pawel1
Kudo Kingpin
7 years ago

Hi,

1st - your formula for grouping hours would work if you sort the hours from the latest to the earliest:

rango_horario = SWITCH(TRUE(),
                    hurto_personal[hora] >= TIME(22,00,00),"10 - 12 pm",
                    hurto_personal[hora] >= TIME(20,00,00),"8 - 10 pm",
                    hurto_personal[hora] >= TIME(18,00,00),"6 - 8 pm",
                    hurto_personal[hora] >= TIME(16,00,00),"4 - 6 pm",
                    hurto_personal[hora] >= TIME(14,00,00),"2 - 4 pm",
                    hurto_personal[hora] >= TIME(12,00,00),"12 - 2 pm",
                    hurto_personal[hora] >= TIME(10,00,00),"10 - 12 am",
                    hurto_personal[hora] >= TIME(08,00,00),"8 - 10 am",
                    hurto_personal[hora] >= TIME(06,00,00),"6 - 8 am",
                    hurto_personal[hora] >= TIME(04,00,00),"4 - 6 am",
                    hurto_personal[hora] >= TIME(02,00,00),"2 - 4 am",
                    hurto_personal[hora] >= TIME(12,00,00),"10 - 12 am","validar")
 
2nd: I assume you need a formula for Compound Annual Growth Rate (CAGR), If so, you can write either a static one (valid for years 2010-2018 only):
 
CAGR 2018vs2010 = 
var Hurtos2010 = 
    CALCULATE([tot_hurtos], 
        Dates[Year] = 2010)
var Hurtos2018 = 
    CALCULATE([tot_hurtos], 
        Dates[Year] = 2018)
Return
(Hurtos2018/Hurtos2010)^(1/8)-1

or a dynamic one (for any selected years):

CAGR dynamic = 
var HurtosFirstYear = 
    CALCULATE([tot_hurtos], 
        FILTER(ALLSELECTED(Dates),Dates[Year] = MIN(Dates[Year])))
var HurtosLastYear = 
    CALCULATE([tot_hurtos], 
        FILTER(ALLSELECTED(Dates),Dates[Year] = MAX(Dates[Year])))
var NumberOfYears = 
    MAX(Dates[Year])-MIN(Dates[Year])
Return
(HurtosLastYear/HurtosFirstYear)^(1/NumberOfYears)-1

aha, and you need a 'Dates' Table with at least two columns 'Date' and 'Year' to make it work.

hope it helps, good luck!

Pawel

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    pawel1 I apologize for the delay in the response but I have been somewhat busy with work issues.
    Both solutions are what I was looking for, simply great, thank you very much for your help master.
    in the average annual growth, if I calculate it directly, that is 159.11% / 8 = 19.8%
    on the other hand, the result is 19,12. It is tolerable, but I would like to know why it can make a difference?
    a more than sorry query to be so annoying, how could you modify the static formula so that it is not affected by the filter context?
    apart from that again, thank you very much for the response, brilliant, plus I learned a new concept of calculation (cagr), which I did not know existed