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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
schaetzles
Frequent Visitor

Calculating monthly sales of the previous year with non contiguous datetable

Hey guys,
I am struggling to do the following task. 

I have 3 columns involving dates:
-one with the year
-one with the month
- one with the first day of the month (e.g January.1.2022)

I calculated the sales for each month and now i want to display the growth of the sales in a table.
Therefore, i need to calculate the sales for each month of the previous year.

This would be quite easy if i could use the previousyear or the sameperiodlastyear function.
However, i can not use these function as i do not have contiguous dates.

What i tried so far is creating a column with both year and month (year*100 + month) with the dax:
Calculate([Total Sales], Date[year*100+ month) -100). Sadly, this does not work.

I would be really glad if someone could offer help



1 ACCEPTED SOLUTION
v-yinliw-msft
Community Support
Community Support

Hi @schaetzles ,

 

You can try this method:

Measures:

 

Total2021 = CALCULATE([Total Sales], FILTER('Date', 'Date'[Year] = YEAR(TODAY()) - 1))
Total2022 = CALCULATE([Total Sales], FILTER('Date', 'Date'[Year] = YEAR(TODAY())))
Total Sales = SUM('Date'[Sales])
Diff = [Total2022] - [Total2021]

vyinliwmsft_0-1668411117409.png

 

 

Hope this helps you. Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

2 REPLIES 2
v-yinliw-msft
Community Support
Community Support

Hi @schaetzles ,

 

You can try this method:

Measures:

 

Total2021 = CALCULATE([Total Sales], FILTER('Date', 'Date'[Year] = YEAR(TODAY()) - 1))
Total2022 = CALCULATE([Total Sales], FILTER('Date', 'Date'[Year] = YEAR(TODAY())))
Total Sales = SUM('Date'[Sales])
Diff = [Total2022] - [Total2021]

vyinliwmsft_0-1668411117409.png

 

 

Hope this helps you. Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

amitchandak
Super User
Super User

@schaetzles , You can create date like

Date = Date([Year], [Month],1)

 

in case month is text

Date = datevalue("01-" &[Month] & "-" & [Year])

 

Not you can join with a date table and use time intellignece

 

Also in other case, you need a separate year, month table joined with you table on year month key

 

With date and date table

 

This month = CALCULATE([Net],DATESMTD(ENDOFMONTH('Date'[Date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

 

With Years and month table(say date)

 

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && Date[Month]=max(Date[Month])))

Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && Date[Month]=max(Period[Month])))

 

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.