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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
peoplehelpme
Helper I
Helper I

want to show previous week data after selecting week

hi guys imagine if i click week1 it will show data of last week of the previous month, please tell me to do that,

in this example below. the present data is week1=128.76 , but if i want to show is when i click w2 it will show 128.76

peoplehelpme_0-1710149881764.png

 

6 REPLIES 6
v-kongfanf-msft
Community Support
Community Support

Hi @peoplehelpme ,

 

Please describe your requirements along with relevant test data.

 

Best Regards,
Adamk Kong

v-kongfanf-msft
Community Support
Community Support

Hi @peoplehelpme ,

 

You need to create a date table and then establish a one-to-many relationship with your data table. Then create the following:

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2024, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
    "Month", MONTH ( [Date] ),
    "Week", WEEKNUM ( [Date] ),
    "Year-Qur",
        YEAR ( [Date] ) & "Q"
            & ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
    "Year-Mon",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Year-Week",
        YEAR ( [Date] ) * 100
            + WEEKNUM ( [Date], 2 ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

vkongfanfmsft_0-1710210022822.png

result_ = 
VAR sel_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR sel_mon =
    SELECTEDVALUE ( 'Date'[Month] )
VAR sel_week =
    SELECTEDVALUE ( 'Date'[Week] )
VAR sel_min =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( 'Date', sel_year && sel_mon && sel_week )
    )
VAR sel_max =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Date', sel_year && sel_mon && sel_week )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        DATESBETWEEN ( 'Date'[Date], sel_min - 7, sel_min - 1 )
    )

vkongfanfmsft_1-1710210068868.png

 

Best Regards,
Adamk Kong

 

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

what column i need to create relationship

Hi @peoplehelpme ,

 

Please provide your data model.

 

Best Regards,
Adamk Kong

Capture.PNG

sorry for unclear target, i string column too

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.