Forum Discussion

MHTANK's avatar
MHTANK
Icon for Helper III rankHelper III
1 year ago
Solved

Previous_Value_as per Day_range

Date Year Month Day_Range Value 09-11-2024   2024 Nov 1-15 41.00 15-11-2024   2024 Nov 1-15 43.00 25-11-2024   2024 Nov 16-30 55.00 29-11-2024   2024 Nov 16-30 58.0...
Need Help
  • Khushidesai0109's avatar
    Khushidesai0109
    1 year ago

    Prev_Value =
    VAR CurrentYear = SELECTEDVALUE('YourTable'[Year])
    VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month_Number])
    VAR CurrentCustom = SELECTEDVALUE('YourTable'[Custom])

    RETURN
    CALCULATE(
    [Avg_Value],
    TOPN(
    1,
    FILTER(
    ALLSELECTED('YourTable'),
    ('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] = CurrentMonth && 'YourTable'[Custom] < CurrentCustom) ||
    ('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] < CurrentMonth) ||
    ('YourTable'[Year] < CurrentYear)
    ),
    'YourTable'[Year], DESC,
    'YourTable'[Month_Number], DESC,
    'YourTable'[Custom], DESC
    )
    )

Khushidesai0109's avatar
Khushidesai0109
Icon for Skilled Sharer rankSkilled Sharer
1 year ago

 

To achieve this in a Power BI matrix, you need to create measures instead of using Power Query. Follow these steps:Avg_Value = AVERAGE('YourTable'[Value])


 

Create the "Prev" Measure

This measure gets the previous period's average dynamically in a matrix visualization:

 

 

Prev_Value =
VAR CurrentYear = SELECTEDVALUE('YourTable'[Year])
VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month])
VAR CurrentDayRange = SELECTEDVALUE('YourTable'[Day_Range])

RETURN
CALCULATE(
[Avg_Value],
FILTER(
ALL('YourTable'),
('YourTable'[Year] = CurrentYear && 'YourTable'[Month] = CurrentMonth && 'YourTable'[Day_Range] < CurrentDayRange) ||
('YourTable'[Year] = CurrentYear && 'YourTable'[Month] < CurrentMonth) ||
('YourTable'[Year] < CurrentYear)
),
LASTNONBLANK('YourTable'[Day_Range], [Avg_Value])
)

Add to the Matrix Visualization

  • Rows: Year, Month, Day_Range
  • Values: Avg_Value, Prev_Value



MHTANK

MHTANK's avatar
MHTANK
Icon for Helper III rankHelper III
1 year ago

Day_Range and Month are in "Text" data type. How "<" posible for that?

  • Khushidesai0109's avatar
    Khushidesai0109
    Icon for Skilled Sharer rankSkilled Sharer
    1 year ago

    Hiii MHTANK 

    Here is the modified DAX measure

     

     

    Prev_Value =
    VAR CurrentYear = SELECTEDVALUE('YourTable'[Year])
    VAR CurrentMonth = VALUE(SELECTEDVALUE('YourTable'[Month])) -- Convert to number
    VAR CurrentDayRange = VALUE(SELECTEDVALUE('YourTable'[Day_Range])) -- Convert to number

    RETURN
    CALCULATE(
    [Avg_Value],
    FILTER(
    ALL('YourTable'),
    ('YourTable'[Year] = CurrentYear && VALUE('YourTable'[Month]) = CurrentMonth && VALUE('YourTable'[Day_Range]) < CurrentDayRange) ||
    ('YourTable'[Year] = CurrentYear && VALUE('YourTable'[Month]) < CurrentMonth) ||
    ('YourTable'[Year] < CurrentYear)
    ),
    LASTNONBLANK('YourTable'[Day_Range], [Avg_Value])
    )

     

     

    If this helps, I would appreciate your KUDOS!
    Did I answer your question? Mark my post as a solution!

    • MHTANK's avatar
      MHTANK
      Icon for Helper III rankHelper III
      1 year ago

      This give me error. cannot convert "Jan" of type text to type number.

      • Khushidesai0109's avatar
        Khushidesai0109
        Icon for Skilled Sharer rankSkilled Sharer
        1 year ago

        Create a Numeric Month calculated Column

        Create a calculated column in your table to convert month names into numbers

         

        Month_Number =
        SWITCH(
        'YourTable'[Month],
        "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6,
        "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12,
        BLANK()
        )

         

         

         

        Modify Your Measure to Use Month_Number

        Prev_Value =
        VAR CurrentYear = SELECTEDVALUE('YourTable'[Year])
        VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month_Number]) -- Use the numeric month column
        VAR CurrentDayRange = VALUE(SELECTEDVALUE('YourTable'[Day_Range])) -- Ensure numeric comparison

        RETURN
        CALCULATE(
        [Avg_Value],
        FILTER(
        ALL('YourTable'),
        ('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] = CurrentMonth && VALUE('YourTable'[Day_Range]) < CurrentDayRange) ||
        ('YourTable'[Year] = CurrentYear && 'YourTable'[Month_Number] < CurrentMonth) ||
        ('YourTable'[Year] < CurrentYear)
        ),
        LASTNONBLANK('YourTable'[Day_Range], [Avg_Value])
        )