conditional column and dax calculations

[Anonymous]

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) 

Now if I go back to the subject,

1st - It occurred to me to create a graph that shows the time when the most thefts are committed, when histogram use does not correctly summarize the values, then I thought about solving it manually creating a range for the hours

something like this: 12 to 2 am 2 to 4 am 4 to 6 am ... etc the column is in the attached report, the issue is that it is not working because I do not know if I am applying the logic wrong, try to do it by query editor and by dax and nothing

the 2nd - I want to calculate the average increase in thefts annually, that is, 19.8% that would correspond to the percentage of increase in each year that can be seen in the waterfall chart between the number of years, I do not know how to recover by dax this value.

if you could help me with this I appreciate it very much

link download report report theft

[pawel1 · Accepted solution]

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]

@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