Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors