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
Spudduk
Frequent Visitor

How do I show in a table the the same field but with a different date range?

Thanks in anticpation... Newbie alert....

 

I have a field X which is linked to a Date. Presently I have no Date Filters on the table so it average the full data set.

 

What i would like to show is the following in the Table

 

Product  A   - X(Over full range)     X - Past 8 weeks      X - Past 4 weeks        X - Past weeks.

 

I can create the individual cards but would like the detail that sits behind it.

 

Thanks

 

Andy

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @Spudduk ,

 

first create a measure for the total X:

 X(Over full range) = SUM(myTable[Sales Amount])

 

And then you can create a measure that will filter on the last 8 weeks:

X - Past 8 weeks =
CALCULATE(
    [X(Over full range)],
    DATESINPERIOD(
        'myDateTable'[Date],
        MAX( 'myDateTable'[Date] ),
        -56,
        DAY
    )
)

 

Past 4 weeks:

X - Past 4 weeks =
CALCULATE(
    [X(Over full range)],
    DATESINPERIOD(
        'myDateTable'[Date],
        MAX( 'myDateTable'[Date] ),
        -28,
        DAY
    )
)

 

And past week:

X - Past week =
CALCULATE(
    [X(Over full range)],
    DATESINPERIOD(
        'myDateTable'[Date],
        MAX( 'myDateTable'[Date] ),
        -7,
        DAY
    )
)

 

If you provide sample data it would be easier to give you a working code.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

1 REPLY 1
selimovd
Super User
Super User

Hey @Spudduk ,

 

first create a measure for the total X:

 X(Over full range) = SUM(myTable[Sales Amount])

 

And then you can create a measure that will filter on the last 8 weeks:

X - Past 8 weeks =
CALCULATE(
    [X(Over full range)],
    DATESINPERIOD(
        'myDateTable'[Date],
        MAX( 'myDateTable'[Date] ),
        -56,
        DAY
    )
)

 

Past 4 weeks:

X - Past 4 weeks =
CALCULATE(
    [X(Over full range)],
    DATESINPERIOD(
        'myDateTable'[Date],
        MAX( 'myDateTable'[Date] ),
        -28,
        DAY
    )
)

 

And past week:

X - Past week =
CALCULATE(
    [X(Over full range)],
    DATESINPERIOD(
        'myDateTable'[Date],
        MAX( 'myDateTable'[Date] ),
        -7,
        DAY
    )
)

 

If you provide sample data it would be easier to give you a working code.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

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.