Sales last 365 days

[intose]

Hi,

I am kind of new to DAX and am tinkering with som formulas, can you give me som insights on this matter?

First of all my approach to sum last 365 days sales and then my failing atempt to use variables to increse readability.

I have 2 separate measures to get last and first sales date from my sales table.

Last sales date:= LASTDATE(ALL(Sales[Datum]))
First sales date:= DATEADD(LASTDATE(ALL(Sales[Datum]));-365;DAY)

and then

Sales 365:= CALCULATE(SUM(Sales[Antal]);DATESBETWEEN(Sales[Datum];[Date first sale];[Date last sale]))

This works as intended from what I can see, any comments on my approach?

In order to reduce number of measures and increse the readability I hav been trying this but only get blank return value. What am I doing wrong?

Sales 365_1:=
Var DateLastSale = LASTDATE(ALL(Sales[Datum]))
Var DateFirstSale= DATEADD(LASTDATE(ALL(Sales[Datum]));-365;DAY)
Return
CALCULATE(SUM(Sales[Qty]);DATESBETWEEN(Sales[Datum];DateLastSale ;DateFirstSale))

Thanx in advance

edit:

Add link to sample file in Excel OneDrive

//INTO

[v-alq-msft · Accepted solution]

Hi, @intose 

If you want to use time intellegence function, you need a date table containing all continuous days for that year.  Moreover, most of the time intelligence functions require a separate Date table to work correctly. I created data to reproduce your scenario. The pbix file is attached at the end.

Table:

Calendar:

Calendar = CALENDARAUTO()

There is a relationship between two tables. You may create a measure as below.

Result1 = 
CALCULATE(
    SUM('Table'[Sales]),
    DATESINPERIOD(
        'Calendar'[Date],
        LASTDATE('Calendar'[Date]),
        -365,
        Day
    )
)

You need to use the 'Date' column from 'Calendar' table to display the result, which contains continuous days.

If you don't want to use time intellegence functions, you may try the following measure.

Result2 = 
var _enddate = SELECTEDVALUE('Table'[Date])
var _startdate = _enddate-365
return
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>=_startdate&&
        'Table'[Date]<=_enddate
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@intose I would go with 2nd measure Sales 365_1  , you need to create separate measures in case you think those measures can be useful in other measures.

As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[intose]

Hi,

Thank you for the fast response!

I have a date table in my model.

But the 2:nd does not work as it only returns blank

//into

[intose]

Here is a link to my sample file Sales 365.xlsx

[parry2k]

@intose you should be using the dates from calendar table in your measures, instead from the sales table.

[intose]

Hi,

But what if I want to get last date from fact table instead of my date dimention?

//into