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
iozkan
Regular Visitor

Defining basis month or year and comparing

Hi guys,

 

I have a table of sales figures with dates (monthly, quarterly, yearly) and I'd like to choose a basis month or year (which will be 100 and should change according to selected month or year but basis should be 100) then compare it as in below. Many thanks!

 

Capture.JPG

2 ACCEPTED SOLUTIONS

@iozkan

 

Bascially I have used a New Table of Dates to slice the original Table

 

A New Table (Calculated Table) was created from the Modeling Tab>>> NEW TABLE

New_Table =
ALL ( TableName[Date] )

Then following MEASURE was added to Original Table

 

Index =
VAR selectedmonthSales =
    CALCULATE (
        SUM ( TableName[Sales] ),
        FILTER (
            ALL ( TableName ),
            TableName[Date] = SELECTEDVALUE ( 'New_Table'[Date] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( TableName[Date] ) = SELECTEDVALUE ( 'New_Table'[Date] ),
        100,
        SELECTEDVALUE ( TableName[Sales] ) * 100
            / selectedmonthSales
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

@iozkan

 

Using Slicer from New Table, now you can get the required Index Measure in your Original Table

 

1026.png


Regards
Zubair

Please try my custom visuals

View solution in original post

11 REPLIES 11

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.