Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    4 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's avatar
Anonymous
Not applicable
4 years ago

Hi Greg_Deckler ,

 

Thanks for quick response! Unfortnetuly my situation is a little bit complex which I still cannot get the value I want.

 

In mine situatie I use 3 differents tables see formule below. I really dont how to fix this.

 

_YTD Target_test1 = 
VAR GMID = ([_KPI Lower Limit] + [_KPI Upper Limit]) / 2 //From FACT_Target table
VAR EINDKWARTAAL = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),ENDOFQUARTER('Dim Kpi'[dat_end]),DAY) //BusinessDate is from Snapshot table and Dim KPI is another table
VAR YTDDAGEN = DATEDIFF(DATE(YEAR([Business_date (current)]),1,1),[Business_date (current)],DAY)
RETURN
GMID * YTDDAGEN/EINDKWARTAAL