Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm facing an issue while calculating previous month sales for the following data:
TimeID | Date | FY | FYQuarter | Month |
1 | 4/1/2013 | 2013 | 1 | April |
2 | 4/1/2014 | 2014 | 1 | April |
3 | 4/1/2015 | 2015 | 1 | April |
4 | 8/1/2013 | 2013 | 2 | August |
5 | 8/1/2014 | 2014 | 2 | August |
6 | 8/1/2015 | 2015 | 2 | August |
7 | 12/1/2013 | 2013 | 3 | December |
8 | 12/1/2014 | 2014 | 3 | December |
9 | 12/1/2015 | 2015 | 3 | December |
10 | 2/1/2014 | 2013 | 4 | February |
11 | 2/1/2015 | 2014 | 4 | February |
12 | 2/1/2016 | 2015 | 4 | February |
13 | 1/1/2014 | 2013 | 4 | January |
14 | 1/1/2016 | 2015 | 4 | January |
15 | 7/1/2013 | 2013 | 2 | July |
16 | 7/1/2014 | 2014 | 2 | July |
17 | 7/1/2015 | 2015 | 2 | July |
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)
Month | Revenue | LastMonthRev |
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?
Solved! Go to Solution.
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
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)))
@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/
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?
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |