cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
meeradominic
Frequent Visitor

Display last 14 days data in a matrix based on slicer selection

Hi All,

 

I have a requirement to display a Power BI report, which has a matrix like below. I want to display a measure (a sum) for only past 14 days from a date selected on slicer. Say if I select 17th January, I should I only see data from 4th January. If I select 16th January--> I should see only data from 3rd January etc. 

meeradominic_0-1673924341716.png

Can anyone help, please!!

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @wolfgangkb-work ,

I create a sample pbix file(see the attachment), please check if that is what you want.

1. Assume that there is one date dimension table, and DO NOT create any relationship with the fact table

yingyinr_2-1674034366048.png

2. Create a measure as below 

Flag =
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _fdate =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( _fdate >= _seldate - 13 && _fdate <= _seldate, 1, 0 )

3. Create a visual and apply the visual-level filter on the visual with the condition(Flag is 1)

yingyinr_3-1674034513314.png

In addition, you can refer the following links to get it.

Show Days Before Or After A Selected Date - Advanced Power BI Visual Techniques

yingyinr_1-1674034286711.png

If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
yingyinr
Community Support
Community Support

Hi @wolfgangkb-work ,

I create a sample pbix file(see the attachment), please check if that is what you want.

1. Assume that there is one date dimension table, and DO NOT create any relationship with the fact table

yingyinr_2-1674034366048.png

2. Create a measure as below 

Flag =
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _fdate =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( _fdate >= _seldate - 13 && _fdate <= _seldate, 1, 0 )

3. Create a visual and apply the visual-level filter on the visual with the condition(Flag is 1)

yingyinr_3-1674034513314.png

In addition, you can refer the following links to get it.

Show Days Before Or After A Selected Date - Advanced Power BI Visual Techniques

yingyinr_1-1674034286711.png

If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

Thank you so much for your answer. I tried your method as it is. And it works perfectly in a table. see below.

meeradominic_0-1674175576772.png

But when I convert it to a matrix nothing shows up. I couldn't figure out why that is.

meeradominic_1-1674175719503.png

But I managed to get required results uisng the other link you shared . Thanks again!!

meeradominic_2-1674176078083.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.