DAX Help

[Anonymous]

I need help creating a DAX measure for the following task in the below dataset

Year	Month	Sales
2019	Jan	100
2019	Feb	110
2019	Mar	120
2019	Apr	130
2019	May	140
2019	Jun	150
2019	Jul	160
2019	Aug	170
2019	Sep	180
2019	Oct	190
2019	Nov	200
2019	Dec	210
2020	Jan	220
2020	Feb	230
2020	Mar	240
2020	Apr	250
2020	May	260

I need May 2020 sales as March 2020 + April 2020, April 2020 sales as March 2020. March 2020 sales needs to March 2019 to Feb 2020 (Sum of sales)

[FrankAT]

Hi @Anonymous 

take a look at the following solution:

Sum of Sales = SUM('Table'[Sales])


Running Total = 
CALCULATE(
    [Sum of Sales],
    FILTER(
        ALLEXCEPT('Table','Table'[Year]),
        'Table'[Date] <= MAX('Table'[Date])
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amitchandak]

@Anonymous , id every month logic is changing if need rolling you can do that with date table

2/3

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Startofmonth(dateadd(Sales[Sales Date],-1,month)),3,MONTH))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Amit, I am using the same logic to create 6 months rolling sums. But for YTD I am failing, I am wondering is there a way to create the same maybe as a calculated column? 

[v-easonf-msft]

Hi, @Anonymous 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason