Skip to main content
cancel
Showing results for 
Search instead 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

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

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
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:

d1.png

 

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.

d2.png

 

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:

d3.png

 

Best Regards

Allan

 

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

 

 

 

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
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:

d1.png

 

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.

d2.png

 

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:

d3.png

 

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
Super User
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/
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!



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.

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
Frequent Visitor

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

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

Hi,

 

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

 

//into

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.