Forum Discussion
DAX formule need help
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!
Hi Anonymous
Total in table visual will show you summarize result. Your measure looks like as below.
_YTD Target_test =
VAR GMID = ([_KPI Lower Limit] + [_KPI Upper Limit]) / 2
VAR B = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY)
VAR A = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY)
RETURN
GMID * (A/B)Your measure is based on many different parts.
Such as [KPI lower limit], [_KPI Upper Limit], B and A. All of them will show summarize result in Total.
Here I create a sample to explain it more to you.
My Sample.
My Measure has same logic like yours.
Measure =
VAR _GMID = DIVIDE(SUM('Table'[KPI lower limit])+SUM('Table'[KPI upper limit]),2)
VAR _A = SUM('Table'[Day1])
VAR _B = SUM('Table'[Day2])
RETURN
_GMID*(_A/_B)Result:
We see Q2 has result 4, this result is based on (3+1)/2 *(2/1)
Total is based on summarize result, (8+8)/2 *(2/6) = 2.67
Here I suggest you to create a new measure based on [_YTD Target_test] measure by Sumx function.
New Measure = SUMX('Table',[Measure])Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous,
I tried your solution, but unfortunately I have the same result. So mabye below information give you more explanation about my situation.
So in the first table I want to see Q2 because in the filter I selected 31-5-2021.In the second table you see the PK of the 3 tables, KPI, Target and Date(snapshot).
So what I need is when I select a business date from the filter that my measure only select the right quarter and the value of my measure for that quarter.
I tried your last measure SUMX(table,...) I tried all the 3 tables no one of them works. In your case you have only 1 table.
Please help Thank you!