Forum Discussion

uj91's avatar
uj91
Icon for Helper I rankHelper I
10 months ago
Solved

to show percentage difference values in a table without having date column in table visual.

I have a field parameter as below:    All.Diff Values_Search = {     ("% Change(MoM) CPC", NAMEOF('Reporting.Search'[Diff CPC_Search]), 0),     ("% Change(MoM) CPM", NAMEOF('Reporting.Search'[Di...
  • v-kpoloju-msft's avatar
    10 months ago

    Hi uj91,

    Thanks for sharing the details and screenshots that really helped clarify the scenario. Also, thanks to Ashish_Mathur,KarinSzilagyi,Hans-Georg_Puls, for those inputs on this thread. 

    The behaviour you are seeing is expected in Power BI. When the Month column is present in the table, each row has a clear date context, so PREVIOUSMONTH() can correctly determine which month to compare against. Once the Month column is removed and replaced with a dimension like Keyword, there’s no longer a row-level date context, so the previous-month calculation returns blank and the % change doesn’t display.

    To keep the MoM % working even when the Month column isn’t shown, the measure needs to explicitly determine the current month inside the DAX and then calculate the previous month independently of the visual layout. You can do that by replacing PREVIOUSMONTH() with the following pattern:

    Combined_Previous Month CPC Value_Search =
    VAR CurrentMonth =
        MAX ( 'Reporting.Search'[MonthSortDate_Search] )
    RETURN
    CALCULATE (
        [CPC_Search],
        FILTER (
            ALL ( 'Reporting.Search'[MonthSortDate_Search] ),
            'Reporting.Search'[MonthSortDate_Search]
                = EOMONTH ( CurrentMonth, -1 )
        )
    )
    

    Then use this updated % Change measure:

    Diff CPC_Search =
    VAR CurrentValue =
        [CPC_Search]
    VAR PreviousValue =
        [Combined_Previous Month CPC Value_Search]
    RETURN
    IF (
        NOT ISBLANK ( PreviousValue ),
        DIVIDE ( CurrentValue - PreviousValue, PreviousValue ),
        0
    )
    

    With this approach, the MoM % values will continue to show correctly even when the table displays Keyword instead of the Month column. This isn’t a limitation of field parameters or the table visual it’s simply how DAX handles time intelligence when date context isn’t present.

    Please give this a try and let us know how it goes. Happy to help further if needed.
    Thank you for using the Microsoft Fabric Community Forum.