Compare this week to last week and some distinct count

[ElliotP]

Afternoon guys, it's been a long time and I'm refreshing my dax again.

I'm trying to calculate this week's sales and then put that on a KPI against a week's sales from four weeks ago.

I have my datetable and data table linked;

This solution I feel is the right way; https://community.powerbi.com/t5/Desktop/How-to-create-Bar-chart-comparing-This-Week-Last-Week-and-T...

P1WNetSales2 = 
VAR CurrentWeekNum =
    MAX ( 'ItemDetail$DateTable'[WeekOfYearISO] )
VAR CurrentYear =
    MAX ( 'ItemDetail$DateTable'[YearKey] )
RETURN
    (
        CALCULATE (
            SUM ( 'ItemDetail$'[Net Sales] ),
            FILTER (
                ALL ( 'ItemDetail$DateTable' ),
                'ItemDetail$DateTable'[YearKey] = CurrentYear
                    && 'ItemDetail$DateTable'[WeekOfYearISO]
                    = CurrentWeekNum - 1
            )
        )
)

But when I enter this as one measure and a measure for 4 week's ago ('changing currentweeknum - 4' at the bottom) I simply recieve blank.

I've tried entering just = CurrentWeekNum without any subtraction to see this week's yet nothing shows up.

Thoughts; I feel like I'm missing something crucial here.

I'd then also like to be able to this using Discountcount (I have some transaction ID's that are unique to each order and I'd like to workout how many unique transaction ID orders there are per week).

[MattAllington]

You haven't described your visuals.  What do you have set up ?

To debug this, I would do the following.

1. Write 2 test measures

CurrentWeekNum =
    MAX ( 'ItemDetail$DateTable'[WeekOfYearISO] )
CurrentYear =
    MAX ( 'ItemDetail$DateTable'[YearKey] )

Put these test measures in your visual to make sure they are working. 

Then write a test table 

 calculated table=   
            FILTER (
                ALL ( 'ItemDetail$DateTable' ),
                'ItemDetail$DateTable'[YearKey] = XXX
                    && 'ItemDetail$DateTable'[WeekOfYearISO]
                    = YYY
            )
        

swap out xxx and yyy for numbers you know are coming from the test measures above and see what happens.  Breaking the problem apart like this should help you identify where the issue is. 

[ElliotP]

I'm ideally trying to use a KPI. One comparing this past week, with the average sum's of each of the past 4 weeks averaged.

I tried your idea and I know my data is bit funny so it's going to throw out oddballs. The measures results in figures appearing.

The table created but didn't show any values (even when i changed my XXX and YY values around).

I'm looking for a sort of

FourWeeksAgoSum = 

Calculate(Sum('ItemDetail$'[NetSales]), FILTER('ItemDetail$Datetable', 'ItemDetail$DateTable'[Weeknum] - 1)

Type thing

[ElliotP]

SumTest = CALCULATE([NetSalesSum],
 FILTER (
        ALL ( 'ExtendedCalendar' ),
        'ExtendedCalendar'[WeekOfYearISO] >= MAX ( 'ExtendedCalendar'[WeekOfYearISO] ) -2
            && 'ExtendedCalendar'[WeekOfYearISO] <= MAX ( 'ExtendedCalendar'[WeekOfYearISO] )))

Maybe using EARLIER instead?

[ElliotP]

RunTot = CALCULATE (
SUM ( 'ItemDetail$'[Net Sales]),
FILTER ( ALL ( ExtendedCalendar ), 'ExtendedCalendar'[WeekOfYearISO] >= MAX ( 'ExtendedCalendar'[WeekOfYearISO] ) - 2 &&'ExtendedCalendar'[WeekOfYearISO] <= MAX ( 'ExtendedCalendar'[WeekOfYearISO] ) -1 )

)

Doesn't seem to want to work for me; not sure why.

Earlier doesn't seem to want to come together either, hmm.

[MattAllington]

Your formulas look fine to me. I suspect is is the KPI visual.  You should set this up as a table first using the test measures I mentioned earlier to verify everything is working.  Then move on from there.

[ElliotP]

I feel it's something else.

It just shows up as blank: https://gyazo.com/f3dff1bbb14adfd684aef3a3dbbbf531

Using this code:

RunTot = CALCULATE (
SUM ( 'ItemDetail$'[Net Sales]),
FILTER ( ALL ( ExtendedCalendar ), 'ExtendedCalendar'[WeekOfYearISO] >= MAX ( 'ExtendedCalendar'[WeekOfYearISO] ) - 0 &&'ExtendedCalendar'[WeekOfYearISO] <= MAX ( 'ExtendedCalendar'[WeekOfYearISO] ) -1 )

)

To find the total of last week.

I have a week number In the data set table using a related function from the date table.

The Date Table has a running week number for the year (between 1-52).

I have the date table and the data table linked with a relationship.

Ideas?

[ElliotP]

I created a new powerbi file, a new bit of sample data. Everything fresh to avoid any interference.

And the measure still isn't working; it simply shows:

I feel it's an issue with the formula not recognising the week number component properly.

[ElliotP]

Last Week = 
VAR CurrentWeekNum =
    MAX ( 'ExtendedCalendar'[WeekOfYearISO] )
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[YearKey] )
RETURN
    (
        CALCULATE (
            SUM ( sample2[Net Sales] ),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[YearKey] = CurrentYear
                    && 'ExtendedCalendar'[WeekOfYearISO]
                    = CurrentWeekNum - 1
            )
        )
)

Using this code on my new test pbix works and produces the correct results.

When I put this into my main pbix it doesn't work; I feel like there could be something to do with the dates and maybe locale? Any ideas?

[ElliotP]

Evening,

I've been following this example and it works perfectly; http://community.powerbi.com/t5/Desktop/How-to-create-Bar-chart-comparing-This-Week-Last-Week-and-Th...

Using this code;

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

I've cleaned up all of the issues with dates, the locales, etc.

I can only seem to display the last week when i use the WeekNum from the date table as the axis value and not my datekey or a format Day calculated column in my date table. Thoughts on how to solve this, I'm really quite stuck.

[ElliotP]

Using this code works with the datekey;

RunningAcrual = CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]),FILTER(ALL(ExtendedCalendar[WeekNum]),'ExtendedCalendar'[WeekNum]<=MAX('ExtendedCalendar'[WeekNum])))

 But I'm not sure how to modify it to show last week, 2weeks before, etc. I've tried adding in a -2 before the <= MAX and has no effect.

[MattAllington]

Well it depends what is in the weeknum column. If it is a unique ID integer starting at 1 and incrementing by 1 across the entire calendar table, then just write this 

last week = CALCULATE(SUM('itemdetailsdogfood$'[Net Sales]),FILTER(ALL(ExtendedCalendar[WeekNum]),'ExtendedCalendar'[WeekNum]=MAX('ExtendedCalendar'[WeekNum])-1))

-2 for 2 weeks ago etc

if hat is not what is in your weeknum column, then add this column first

read my article about time intelligence if you want to understand how it works - I  cover it  in detail

http://exceleratorbi.com.au/dax-time-intelligence-beginners/

[ElliotP]

Evening,

Thanks for the quick response. I've tried the code and it works but only with the WeekNum as the axis value; I'm unable to use the Datekey or any other colum from my date table (to show the dates a little more nicely); thoughts?

I'm reading your website now as well.

[ElliotP]

Fantastic page introduction. Since we're working with custom time intelligence functions (week number); I'm unsure as to how to bridge that gap to be able use/show the datekey on my visual.

[MattAllington]

I'm not clear what the datekey is. Is it simply a date column?  The trick to moving forward with dax is to be able to "visualise" what is happening the tables in the data model as a result of your formulas. If I am interpreting this correctly, you currently have formulas that work as you want at a week granularity, but not a day granularity (I hope that is a correct assumption).   I'm not really sure how that would work though.  In my experience, companies tend to have a defined week with an ID. E.g. It may start Sunday to Saturday. There would be no need for day reporting when looking at weekly results. 

I realise I am probably missing something. 

[ElliotP]

Evening, thanks for the quick response.

Agreed, I have the formula's working as I want; but I'm not able to visualise this data in the manner I want.

I have a date table with all the usual bits and pieces, as well as ofcourse the corresponding date and week number in my data table.

I'd like to be able to show for example the last week of revenue. So it's perpetually moving. At the moment, I'm only able to do this with the "Weeknumber" showing as "3" or "2" for example; as opposed to being able to show the day or date. So instead of showing the week number as "2" on the axis; it would be amazing to be able to show each day of the past week as "Fri 21".

Thoughts?

[MattAllington]

When you say each day of the last week, do you mean to show Fri 21, Fri 14, Fri 7 etc, or do you mean Fri 21, Thu 20, Wed 19 etc?  If you mean the former, I assume you can just create a mirror column for your week number column that has the Friday End date. If you mean the latter, then I don't understand - maybe a picture would help. 

[ElliotP]

I mean the latter unfortunanetly.

As I'm typing this; I'm getting the feeling perhaps I could just do this with a datesinperiod function just to visualise it on a line graph.

https://gyazo.com/37b3189e082949d88a35f7e75a2801cf

For the KPI's and things I can use our code.

I'll work on it tomorrow, thank you so much; Seems after I cleaned up my dates and locales, I've gone around in circles but finished in the right place. Thank you very much; I greatly appreciate it.

[ElliotP]

Morning; what a tremendous effort for something so simple.

Last Week1 = 
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
            )
        )
)

This is the code I ended up using. I was looking at the graph and unable to work out why just the past two weeks weren't showing and then I was worried we didn't have the solution.

When I put this into a KPI it shows me the sum of the correct week.

[ElliotP]

I'm finding it's not possible to use my last week measure as a value in visuals such as Cards, Sunbursts, etc. Any idea how to help this along? I've thought of creating it as a column but I'm not sure on the formula (after trying).

[ElliotP]

On certain visuals like the bar chart, card, etc the Measure isn't appearing and to be honest I have no absolutly no clue why.