Finding the trend of last 12 months

[dylanjames1947]

Thanks in advance,

In our report we have filters on the left and graphs on the right side. Once the filter is selected the graph needs to show the trend of the last 12 months from the date selected on the line graph. We tried follwing solution link. But we cannot get the expected output as the slicer filters the record as we apply the link .

We need override the slicer properties for this graph to work as expected. Kindly help to find the solution for it.

[LivioLanzo]

Check this one out:

https://community.powerbi.com/t5/Desktop/Dynamic-trend-analysis-based-on-the-selected-month/m-p/5294...

[dylanjames1947]

We have already implemented trend analysis based on the date values, the problem arises when performing additional filteration that is done on the same data table, filters like department, organization unit, company, etc. When we apply these filters the graph shows only one month record.

It would be great if you could help with this problem.

[v-frfei-msft]

Hi @dylanjames1947,

Kindly share your sample data and excepted result to me.

Regards,

Frank

[dylanjames1947]

Here is the link to the sample .pbix file, where we are showing trend for the past 6 months. On selecting a month, we need to show the trend for the past 6 months with the selected filter, but also show the selected details of that date.

Let me know if you require more information. Thanks in advance

[v-frfei-msft · Accepted solution]

Hi @dylanjames1947,

Please refer to the following steps.

1. Create a calculated table.

Table = CROSSJOIN(VALUES('Summary Data'[Month]),VALUES('Summary Data'[Year]))

2. Create a calcualted column in the Summary Data table.

date = DATE('Summary Data'[Year],'Summary Data'[Month],1)

3. Create a measure to achieve our goal.

Measure 4 = 
VAR datesele =
    DATE ( SELECTEDVALUE ( 'Table'[Year] ), SELECTEDVALUE ( 'Table'[Month] ), 1 )
VAR seleyear =
    SELECTEDVALUE ( 'Summary Data'[Year] )
VAR selemon =
    SELECTEDVALUE ( 'Summary Data'[Month] )
VAR last6month1 =
    DATE ( SELECTEDVALUE ( 'Table'[Year] ), SELECTEDVALUE ( 'Table'[Month] ) - 6, 1 )
VAR last6month2 =
    DATE ( SELECTEDVALUE ( 'Table'[Year] ) - 1, 12
        - ( 6 - SELECTEDVALUE ( 'Table'[Month] ) ), 1 )
RETURN
    IF (
        selemon >= 7,
        IF (
            MAX ( 'Summary Data'[date] ) <= datesele
                && MAX ( 'Summary Data'[date] ) >= last6month1,
            SUM ( 'Summary Data'[CurrentCount] ),
            BLANK ()
        ),
        IF (
            MAX ( 'Summary Data'[date] ) <= datesele
                && MAX ( 'Summary Data'[date] ) >= last6month2,
            SUM ( 'Summary Data'[CurrentCount] ),
            BLANK ()
        )
    )

4. For the table visual, we can put the measure into it and make the measure is not blank.

Also please find the pbix as attached.

Regards,

Frank

[dylanjames1947]

Thanks so much Frank for the detailed solution, it's really really helpful for me.