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
ElliotP
Post Prodigy
Post Prodigy

Sunburst Measures as Values not appearing

Morning;

 

I'm trying to use the Sunburst Visual to show the last week and the week before that's sales for the section from which they were sold as well as the day of the week they were sold in.

 

When I use the column value it appears; but shows all of the data.

When I use my past week measure as the Value; it doesn't show anything and I'm not sure why?

 

P1WNetSales = 
VAR CurrentWeekNum =
    MAX ( 'ExtendedCalendar'[WeekNum] ) - 1
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
RETURN
    (
        CALCULATE (
            SUM ( 'itemdetailsdogfood$'[Net Sales] ),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
)

18d0808c96bd6faed82ab1471fbf3dbb.png

 

Do I need to make it a column instead? I've tried copying the code and nothing appears in the column.

1 ACCEPTED SOLUTION

Hi @ElliotP,

 

After looking into your shared pbix file, I found that the variable "CurrentWeekNum" should be defined as below in order to get the max week of current year, or it will return the max week of all years(not only the current year).Smiley Happy

VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 2,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )

 And following is the formula to create the measure. And it can be shown on the Card visual or on the Chart with a Date column.

 

P2WNetSales =
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 2,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            SUM ( 'itemdetailsdogfood$'[Net Sales] ),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
    )

 

Regards

View solution in original post

16 REPLIES 16

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.