Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

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) Smiley Frustrated

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

1 ACCEPTED SOLUTION
pawel1
Kudo Kingpin
Kudo Kingpin

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

View solution in original post

2 REPLIES 2
pawel1
Kudo Kingpin
Kudo Kingpin

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
Not applicable

@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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors