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
disha_khanna
Regular Visitor

Calculate the month-over-month

I'm facing an issue while calculating previous month sales for the following data:

 

TimeIDDateFY FYQuarterMonth
14/1/201320131April
24/1/201420141April
34/1/201520151April
48/1/201320132August
58/1/201420142August
68/1/201520152August
712/1/201320133December
812/1/201420143December
912/1/201520153December
102/1/201420134February
112/1/201520144February
122/1/201620154February
131/1/201420134January
141/1/201620154January
157/1/201320132July
167/1/201420142July
177/1/201520152July

 

I simply have the month and Year information. The day information was not available in th raw data and the 1st day in every date is added by me. This Date table stores the Fiscal year that begins in April.

I have calculated the revenue using Sales from a Fact Table. I now want to calculate the previous month's revenue. I used the DATEADD Function as shown below:

LastMonthRev = CALCULATE([Revenue],FILTER(DimTime,DATEADD(DimTime[Date],-1,MONTH)))

 

However I get the same value as this month's revenue(refer the table shown below)

MonthRevenueLastMonthRev
1$10.00$10
2$20.00$20
3$30.00$30
4$40.00 
5$50.00$50
6$60.00$60
7$70.00$70
8$80.00$80
9$90.00$90
10$100.00$100
11$110.00$110
12$120.00$120

This data is just for one year. Can someone please let me know what am I doing wrong?

1 ACCEPTED SOLUTION

@disha_khanna

 

For many of the time related functions to work properly please ensure the following in the Calendar table

 

1. Have a column that has the FullDate in the format (DD/MM/YYYY)

2. Have a column called DateKey in the format (YYYYMMDD) taken from the FullDate

3. In your fact table build the date key also in the same format as (YYYYMMDD) using the transaction date that will be in the (DD/MM/YYYY).

4. Join the fact table and Calendar table using this datekey ( format YYYYMMDD ) from the two tables 

 

5. To get your previous month sales just use the measure like

 

            PMonthRevenue:=CALCULATE(sum[Revenue], PREVIOUSMONTH(Calendar[FullDate]))

 

6.  Now plot the Revenue and PMonthRevenue in the graph / table.

 

This should work.

 

If it works please accpet this as Solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Dont think you need the filter:

LastMonthRev = CALCULATE([Revenue], DATEADD(DimTime[Date],-1,MONTH))

 

If I lied, then you just need some flavor of ALL()

LastMonthRev = CALCULATE([Revenue],FILTER(ALL(DimTime),DATEADD(DimTime[Date],-1,MONTH)))

 

 

@disha_khanna

@Anonymous is correct. You do not need the "FILTER" portion of the calculation.

If removing the filter reference does not work then you may want to look at the structure of your date table. In order to use time intelligence you need a date table with contingious dates (none missing). It appears you have a lot missing. You should add a complete table that is customized to your fiscal year. Then, apply months to your graph from the date table. Be sure you build your relationship based upon the date key (complete month/day/year).

See this blog for more information:

http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/

The second link below helps you customize your own calendar.

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Anonymous : I tried to create a table that held a custom range of dates from 4/1/2013 to 4/1/2016. But still the DATEADD function does not work as expected. I tried to calculate last year's revenue (which was working previously) but it returned incorrect values. Looks like the DATEADD function isn't working. Is it something related to the context of DATEADD function?

 

@disha_khanna

 

For many of the time related functions to work properly please ensure the following in the Calendar table

 

1. Have a column that has the FullDate in the format (DD/MM/YYYY)

2. Have a column called DateKey in the format (YYYYMMDD) taken from the FullDate

3. In your fact table build the date key also in the same format as (YYYYMMDD) using the transaction date that will be in the (DD/MM/YYYY).

4. Join the fact table and Calendar table using this datekey ( format YYYYMMDD ) from the two tables 

 

5. To get your previous month sales just use the measure like

 

            PMonthRevenue:=CALCULATE(sum[Revenue], PREVIOUSMONTH(Calendar[FullDate]))

 

6.  Now plot the Revenue and PMonthRevenue in the graph / table.

 

This should work.

 

If it works please accpet this as Solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thanks a lot @CheenuSing, @kcantor and @Anonymous for all your help! 🙂

 

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.