Forum Discussion
Anonymous
4 years agoNot applicable
DAX formule need help
Hi guys, I am struggeling with the following problem. I have below DAX formule and Ithe value that I am getting in the row is correct, but I realy dont understand why I get another value by the t...
- Anonymous4 years ago
Hi Anonymous and Greg_Deckler ,
I have finaly the solution. The issue was that there were no ratio between the table KPI and Date table. So I fixed this with the following formule.
_YTD_Target_Gauge = VAR KWARTAAL = SWITCH( TRUE(), [Business_date (current)] >= DATE(YEAR([Business_date (current)]),1,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),3,31), "Q1", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),4,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),6,30), "Q2", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),7,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),9,30), "Q3", [Business_date (current)] >= DATE(YEAR([Business_date (current)]),10,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),12,31), "Q4") VAR GMID = CALCULATE((SUM('Fact Target'[amt_lower_limit]) + SUM('Fact Target'[amt_upper_limit])) / 2,LEFT('Dim Kpi'[kpi_name],2) = KWARTAAL) VAR EINDKWARTAAL = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY) VAR YTDDAGEN = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY) RETURN GMID*(YTDDAGEN/EINDKWARTAAL)And then another formule which Rico gave me:
TGT_Gauge = SUMX('Dim Kpi',[_YTD_Target_Gauge])Thank you guys to thinking about this issue!
Anonymous
4 years agoNot applicable
Hi Anonymous and Greg_Deckler ,
I have finaly the solution. The issue was that there were no ratio between the table KPI and Date table. So I fixed this with the following formule.
_YTD_Target_Gauge =
VAR KWARTAAL =
SWITCH(
TRUE(),
[Business_date (current)] >= DATE(YEAR([Business_date (current)]),1,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),3,31), "Q1",
[Business_date (current)] >= DATE(YEAR([Business_date (current)]),4,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),6,30), "Q2",
[Business_date (current)] >= DATE(YEAR([Business_date (current)]),7,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),9,30), "Q3",
[Business_date (current)] >= DATE(YEAR([Business_date (current)]),10,1) && [Business_date (current)] <= DATE(YEAR([Business_date (current)]),12,31), "Q4")
VAR GMID = CALCULATE((SUM('Fact Target'[amt_lower_limit]) + SUM('Fact Target'[amt_upper_limit])) / 2,LEFT('Dim Kpi'[kpi_name],2) = KWARTAAL)
VAR EINDKWARTAAL = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY)
VAR YTDDAGEN = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY)
RETURN
GMID*(YTDDAGEN/EINDKWARTAAL)And then another formule which Rico gave me:
TGT_Gauge = SUMX('Dim Kpi',[_YTD_Target_Gauge])
Thank you guys to thinking about this issue!