Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Inherited report with mal-functioning DAX - QTD values are way off

    Posted 9 days ago
    This apparently WAS working but - without any change to the data besides time passing - is now reporting values that are way too low. Any help or pointers would be greatly appreciated

    Code:

    SelectedElapsedTime =
    // Define some variables
    // Selected Month in Filter
    VAR Mth = [SelectedKPI_Month] // Current Month
    VAR CurrMth = [Current_Mth] // Selected Quarter in Filter
    VAR Qtr = [SelectedKPI_Qtr] // Current Quarter
    VAR CurrQtr = [Current_Qtr] // Total Days = Denominator
    VAR TD = [SelectedTotalDays] // FY - Used for both FY & YTD Numerator
    VAR FY =
        CALCULATE (
            SUM ( vw_FiscalCal[isShippingDay] ),
            KEEPFILTERS ( vw_FiscalCal[Is_YTD_Elapsed] = "Yes" ),
            KEEPFILTERS ( vw_FiscalCal[Is_Current_Yr] = "Yes" )
        ) // QTD Numerator
    // Chosen Month = Current Month
    VAR QTD1A =
        CALCULATE (
            SUM ( vw_FiscalCal[isShippingDay] ),
            vw_FiscalCal[FiscalQuarter] = Qtr,
            vw_FiscalCal[Is_YTD_Elapsed] = "Yes",
            vw_FiscalCal[Is_Current_Yr] = "Yes"
        ) // Chosen Month > Current Month
    VAR QTD1B =
        CALCULATE (
            SUM ( vw_FiscalCal[isShippingDay] ),
            KEEPFILTERS ( vw_FiscalCal[FiscalQuarter] = Qtr ),
            KEEPFILTERS ( vw_FiscalCal[Is_Current_Yr] = "Yes" )
        ) // MTD Numerator    
    VAR MTD1 =
        CALCULATE (
            SUM ( vw_FiscalCal[isShippingDay] ),
            vw_FiscalCal[Is_YTD_Elapsed] = "Yes",
            vw_FiscalCal[Is_Current_Yr] = "Yes",
            vw_FiscalCal[Is_Current_Month] = "Yes"
        ) // MTD Numerator    
    VAR MTD2 =
        CALCULATE (
            SUM ( vw_FiscalCal[isShippingDay] ),
            vw_FiscalCal[Is_YTD] = "Yes",
            vw_FiscalCal[Is_Current_Yr] = "Yes",
            vw_FiscalCal[Is_Current_Month] = "Yes"
        )
    RETURN
        SWITCH (
            [SelectedKPI_Time],
            // FY
            1, DIVIDE ( FY, TD ),
            // YTD
            2, IF ( Mth < CurrMth, 1, DIVIDE ( FY, TD ) ),
            // QTD
            3,
                IF (
                    Mth < CurrMth,
                    1,
                    IF ( Mth = CurrMth, DIVIDE ( QTD1A, TD ), DIVIDE ( QTD1B, TD ) )
                ),
            // MTD
            4, IF ( Mth = CurrMth, DIVIDE ( MTD1, TD ), 1 )
        )
    Thanks!

    ------------------------------
    DUNCAN FAIRWEATHER
    91362
    ------------------------------


  • 2.  RE: Inherited report with mal-functioning DAX - QTD values are way off

    Bronze Contributor
    Posted 9 days ago
    You need to provide more context on what the calculation is for.

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 3.  RE: Inherited report with mal-functioning DAX - QTD values are way off

    Posted 7 days ago
    It is supposed to show elapsed time as a percentage for Quarter, Month, etc.

    ------------------------------
    DUNCAN FAIRWEATHER
    91362
    ------------------------------