Total Sales Last week Calculation DAX - Non Standard date

Nurry90 · October 29, 2020, 12:09am

Please see attached PBIX sample file regarding my query around Total Last Week calculations (Link to original query - Non-Standard Time Intelligence - #5 by Greg) .

I have a relative date filter on the visual also, to only show the last 3 weeks as this is the only way I can display the most recent data. Is there any way I can build this into the calculation without having a filter on the page?

Sample PBIX file EnterpriseDNA1.pbix (420.3 KB) Sample PBIX file EnterpriseDNA1.pbix (420.3 KB)

Thanks in advance for your help

samharishk · October 29, 2020, 3:06am

@Nurry90 Hey Mate , You can try this

Try This Dax
last 3 week sale =

= CALCULATE([TotalSales],

DATESINPERIOD('Date Table'[Date],

SELECTEDVALUE('Sales Info'[Wk Beginning]),-21,DAY)

)

Give wk beginning date from sales table . I hope this will work for you .

Sample PBIX file EnterpriseDNA1.pbix (421.7 KB)

Cheers

Nurry90 · October 29, 2020, 3:33am

Hi @samharishk,

Thanks for getting back to me. Not sure if the above is what I’m looking for. I need to get the sales amount by product for the previous week. Your formula is giving the same sales for “total sales” “total sales last week” and " last 3 week sale" .

My formula for total sales is the total over many years(in my own dataset) as I cannot figure out how to get a dynamic total YTD calculation. I am not sure if this is why my calculations are not working out for me.

Time intelligence is not my friend, especially because I have a complex date table.

samharishk · October 29, 2020, 4:01am

@Nurry90 .For Last week sale

last week sale =

CALCULATE([TotalSales],

DATESINPERIOD(‘Date Table’[Date],

SELECTEDVALUE(‘Sales Info’[Wk Beginning]),-7,DAY)

)

You can also try these formulas ,
YTD Sales = CALCULATE ([TotalSales], DATESYTD((‘Date’[Date]),“12/31”))
This Sales = CALCULATE ([TotalSales], DATESYTD((ENDOFYEAR(‘Date’[Date])),“12/31”))
Last YTD Sales = CALCULATE ([TotalSales]), DATESYTD (Dateadd(‘Date’[Date], -1, Year),“12/31”))
Last YTD complete Sales = CALCULATE ([TotalSales], DATESYTD(ENDOFYEAR(Dateadd(‘Date’[Date],-1,Year)),“12/31”))
To get the best of the time intelligence function.
Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer:
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

michael66 · October 29, 2020, 9:39am

Hi @Nurry90

Not sure I have understood your question but I provide a formula using DATESBETWEEN and you can select have far back you would like to go, 7 14 21 days.
See New Total Last Week.

Cheers
MichaelSample PBIX file EnterpriseDNA1.pbix (420.3 KB)

michael66 · October 29, 2020, 9:56am

Hi @Nurry90
Please ignore my last post I posted the wrong file. Sorry.

Here is the correct file Sample PBIX file EnterpriseDNA1 (1).pbix (420.4 KB)

Nurry90 · October 29, 2020, 10:14am

Hi @michael66,

The calculation works fine when I use the FinYear & Week as part of the table, but once applied to a table with the products, it adds the total this week to the total last week.

See below - Product 1 Total sales for wk 19/10/2020 = $285.10, the total the prior week 12/10/2020 = $216.10. Once you remove the “wk Beginning” you can see that instead of the total last week showing $216.10, it is $501.20 (this weeks sales, and last weeks sales added together). Not sure if I am explaining this correctly, or if I am just missing something?!

michael66 · October 29, 2020, 11:03am

Hi @Nurry90

I hope I am not misleading you or misunderstanding you.
Try using the Wk Beginning from the Date Table not the Sales Info.
Should look like this.

Cheers
Michael

Sample PBIX file EnterpriseDNA1 (1).pbix (420.6 KB)

Nurry90 · October 29, 2020, 11:42am

Hi @michael66,

Thanks for your help here. Not sure how to explain this, sorry! I want to have a table which shows, Product, Total Sales and Total Sales Last Week, I don’t want to have the date shown, or the week number. So product 1 Total sales (this week) is $285.10 and Total Sales Last week should show $216.10, not $501.20.

Similarly, product 101, total sales(this week) is $906.39 and total sales last week is $254.89, not $1161.28.

There is something wrong in the below measure, that is adding the sales together.

total last week = 
VAR _ListOfWeeks = SELECTCOLUMNS(
    ADDCOLUMNS(
        VALUES( 'Date Table'[FYWeeknYear]),
        "@LW", CALCULATE( 
            MAX( 'Date Table'[FYWeeknYear] ),
            FILTER( 
                ALL( 'Date Table' ), 
                'Date Table'[FYWeeknYear] < EARLIER ('Date Table'[FYWeeknYear] ) )
        )
    ),
    "Weeks", [@LW] 
) 
VAR _Result = SUMX( 
    FILTER( 
        ALL( 'Date Table' ), 
        'Date Table'[FYWeeknYear] IN _ListOfWeeks ), 
    [TotalSales]
)

RETURN
_Result

Sorry to be bothering you! But very appreciative of the help

Thanks,
Noelle

michael66 · October 29, 2020, 8:07pm

Hi Noelle
If you don’t show the date in some form you lose Context don’t you. Date Context.
How does PBI know what is this week vs last week vs the weeks before that.
I have done another table where I have left the Wk Beginning in but through formatting you don’t see this field.
I also added a slicer so you can select the time period.Sample PBIX file EnterpriseDNA1 (1).pbix (421.9 KB)
Hope this helps, because that’s all I’ve got. I will watch and see with interest what others say.
My apologies for not solving your issue, if this does not work for you.
Michael

EnterpriseDNA · November 2, 2020, 2:19am

Hi @Nurry90, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. Thanks!

EnterpriseDNA · November 3, 2020, 2:24am

Hi @Nurry90, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Nurry90 · November 4, 2020, 10:08pm

Hi @michael66,

Thank you for trying Not the solution I was looking for as it is still adding the previous week to the current week. Not to worry, I’ll work through what you have given me and see if I can figure it out!

Thanks again! Your help is greatly appreciated

samaguire · November 5, 2020, 12:13am

The easiest way to tackle this is to add a new column to your date table that evaluates to either “Current Week”, “Last Week” based on the date or the relative week (as is available in the practice dataset), or blank().

Once you have this, you’ll be able to use a matrix visual. The new calculated column as the columns, and the products as rows. A bit like this, where I’ve used the relative weeks on the columns:

EnterpriseDNA · November 9, 2020, 2:43am

Hi @Nurry90, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!