Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Need help

Requirement

-- Add the parameters for the stored procedure here

--@endofquarter user will select from slicer date picker
    @endofquarter datetime

 

   --@yearstart will get from @endofquarter
    declare @yearstart datetime
    SET        @yearstart = DATEADD(yy, DATEDIFF(yy, 0, @endofquarter), 0)    

 

-- This SQL query i need to implememt in power bi 


    Select    SUM(CASE    
            WHEN full_date >= @yearstart THEN total_paid
            ELSE 0
            END) as YTD_Paid
    from    fact.fact_table
    where    Full_Date>=@endofquarter

-----------------------------------------------------------------------------

I tried the below DAX query in Power Bi, but it did not work. Please assist me.

 

Ytd_PD =
Var endofquarter=MAX('Date'[Date]) //created date table for the date picker (@endofquarter)
var  yearstart= DATE(YEAR(Max('Date'[Date])),1,1)
//Var SumofTd_Pd=SUM(fact_table[total_paid])
Var Result=
            CALCULATE(SUM(fact_table[total_paid]),
            FILTER( facttable,
            IF(
               MAX(fact_table[Full_date])>=yearstart,fact_table[total_paid],0
                ) &&
                fact_table[Full_date] <=endofquarter   
            )
            

Return Result


1 Reply

  • vanessafvg's avatar
    vanessafvg
    Community Champion

    not sure if i quite understand your requirement but i think if you move your if statement that willl help

     

    Ytd_PD =
    VAR endofquarter =
    MAX ( 'Date'[Date] )
    VAR yearstart =
    DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 1, 1 )
    VAR SumofTd_Pd =
    SUM ( fact_table[total_paid] )
    VAR Result =
    CALCULATE (
    SUM ( fact_table[total_paid] ),
    FILTER (
    fact_table,
    fact_table[Full_date] >= yearstart
    && fact_table[Full_date] <= endofquarter
    )
    )
    RETURN
    IF ( ISBLANK ( Result ), 0, Result )