Forum Discussion

tomas12344's avatar
tomas12344
Icon for Helper III rankHelper III
2 years ago

KPI snapshot and trend visualization

Hi All

Need to create a report, which has the following

- a measure A, which is a KPI. this is shown in a KPI Card wiht a target as previous year value of this KPI

- calendar table and coresponing month date slicer in the report. Base on this the snapshot date (month) is selected for the Card. there is a relation between calendar and fact table (where KPI value is)

- need a line/column chart of the KPI trend - from measure A. The time period for the chart should be previous 1 year, show all values by month.

Question: Is there an other way how to do this, without using a bridge table (beween calnedar and fact) which will define the period for the line/column chart? 

 

Need Help
Tips and Tricks

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi tomas12344 -Yes, you can achieve this without using a bridge table.

    hope for measure for total and previous year kpis has been calculated.

    Use the Calendar table’s Month column as a slicer in your report.Create a measure to capture the selected month

    Selected Month = SELECTEDVALUE(Calendar[Month])

    Create a measure to calculate the KPI value for the past 12 months as below

    KPI Trend =
    VAR CurrentDate = MAX(Calendar[Date])
    VAR StartDate = EOMONTH(CurrentDate, -12) + 1
    RETURN
    CALCULATE(
    [Measure A],
    FILTER(
    ALL(Calendar),
    Calendar[Date] >= StartDate && Calendar[Date] <= CurrentDate
    )
    )

     

    this helps on you can create the desired report with a KPI card, month date slicer, and a KPI trend chart without the need for a bridge table.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

  • tomas12344's avatar
    tomas12344
    Icon for Helper III rankHelper III
    2 years ago

    Hi rajendraongole1 

    Thank you for the answer, but what would you put on X axis? Calendar[Date]? Would this work, when the same will be in the slicer, and there will be selecte only 1 month? Base on this slicer the Measure A will be shown, so only one month will be selected. (both the Card with Measure A and trend chart should react on the changes of the month slicer)