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...
  • 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!