Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
simrantuli
Continued Contributor
Continued Contributor

Show data for the past months basis slider selection

Hi All,

I am working on a usage metrics report for reports published in Power BI workspace.

I have a requirement wherein I want the user to be able to filter the report on the basis of monthly no. of Views and Viewers. So far I have been able to achieve this.

The problem is that I want the trend chart in my report to show no. of views and viewers for past months as well for only those reports for which the no. of views and viewers lie between the selected range in Monthly Report Views and Monthly Report Viewers sliders for the selected Month.

You can view the PBIX here: PBIX Google drive link 

For e.g. in the attached report, if I select 1 to 2 in Monthly Report Views slider and the selected Month is March 2021, on the right chart (which shows data for the selected month), I can see only Finance Dashboard and WFH Survey Dashboard as the monthly views for them is between 1 to 2. 

Now, in the trend chart on the left, the number that I see for March 2021 is correct. However, the number for Feb 2021 is not as per my expectations. The Feb 2021 number shows us no. of Views for those dashboards for which no. of Views lies between 1 to 2. However, I want to see the no. of Views for the same dashboards as March 2021 (i.e. Finance Dashboard and WFH Survey Dashboard) for Feb 2021 and Jan 2021 as well. How can I achieve this?

simrantuli_0-1623777008068.png

You can view the PBIX here: PBIX Google drive link 

Your help would be greatly appreciated.

Thanks!

@Fowmy @Jihwan_Kim @Greg_Deckler @amitchandak @parry2k @selimovd 

1 ACCEPTED SOLUTION
simrantuli
Continued Contributor
Continued Contributor

I have achieved the result. I modified the measure as below.

 

Views_Trend = 
VAR MAXDATE =
    LASTDATE ( Calendar2[Month_Year] )
VAR temptable =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Fact' ),
                'Fact'[Report Name],
                'Calendar'[Month_Year],
                'Fact'[Views]
            ),
            'Calendar'[Month_Year] = MAXDATE
        ),
        "Datemonth", 'Fact'[Report Name]
    )
RETURN
IF (
        SELECTEDVALUE ( 'Fact'[Month_Year] ) < MAXDATE,
        CALCULATE (
            COUNT('Fact'[User Name]),
            'Fact'[Report Name] IN temptable,
            ALL('Fact'[Views])
        )
        ,CALCULATE(COUNT('Fact'[User Name]),'Fact'[Month_Year]=MAXDATE)
    )

Thank you so much for your help @MFelix . Your reply guided me towards the solution.

 

View solution in original post

4 REPLIES 4
SeanLJH
New Member

HI all, the  PBIX file: PBIX Google drive link  does ot seem to work no more... any chnace of republishing it or help with how i can view which months are populated in my below view?  

 

Sorry for the mess was handed the usage page but can not understand the monthly views!

 

SeanLJH_0-1676279005693.png

 

simrantuli
Continued Contributor
Continued Contributor

I have achieved the result. I modified the measure as below.

 

Views_Trend = 
VAR MAXDATE =
    LASTDATE ( Calendar2[Month_Year] )
VAR temptable =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Fact' ),
                'Fact'[Report Name],
                'Calendar'[Month_Year],
                'Fact'[Views]
            ),
            'Calendar'[Month_Year] = MAXDATE
        ),
        "Datemonth", 'Fact'[Report Name]
    )
RETURN
IF (
        SELECTEDVALUE ( 'Fact'[Month_Year] ) < MAXDATE,
        CALCULATE (
            COUNT('Fact'[User Name]),
            'Fact'[Report Name] IN temptable,
            ALL('Fact'[Views])
        )
        ,CALCULATE(COUNT('Fact'[User Name]),'Fact'[Month_Year]=MAXDATE)
    )

Thank you so much for your help @MFelix . Your reply guided me towards the solution.

 

MFelix
Super User
Super User

Hi @simrantuli ,

 

If I understand your need correctly you want to pick up for february  2 and january 3 that are the views that correspond to Finance and WFH:

MFelix_0-1623926352581.png

 

 

What I did was to create a measure for the count:

ViewsCount = COUNT('Fact'[User Name])

Now I have made the following changes to your measure:

Views_Trend =
VAR MAXDATE =
    LASTDATE ( Calendar2[Month_Year] )
VAR temptable =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Fact' ),
                'Fact'[Report Name],
                'Calendar'[Month_Year],
                'Fact'[Views]
            ),
            'Calendar'[Month_Year] = MAXDATE
        ),
        "Datemonth", 'Fact'[Report Name]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Fact'[Month_Year] ) <= MAXDATE,
        CALCULATE (
            [ViewsCount],
            FILTER (
                ALL ( 'Fact'[Month_Year] ),
                'Fact'[Month_Year] = MAX ( 'Calendar'[Month_Year] )
            ),
            'Fact'[Report Name] IN temptable
        )
    )

 

Result below and in attach PBIX file. For the other measure you just need to make similar measure but adjust the first formula.

MFelix_1-1623926515566.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



simrantuli
Continued Contributor
Continued Contributor

Hi @MFelix ,

Thank you so much for your reply.

However, it still doesn't fulfil my requirement. 

In the screenshot below, the views for Feb should show 5 as Finance Dashboard and WFH Survey were viewed 5 times in Feb.

simrantuli_0-1623947915624.png

simrantuli_1-1623947983497.png

Can you please help to achieve this?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.