cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Sales last 365 days

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]))

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]))
Return
CALCULATE(SUM(Sales[Qty]);DATESBETWEEN(Sales[Datum];DateLastSale ;DateFirstSale))``````

edit:

//INTO

1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Community Support

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.

Super User

@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/

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!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Frequent Visitor

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

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Hi,

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

//into

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors