Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
So I am running into troubles create a report. I need to report the last 6 months but then each month. For example:
Right now I need month 2015 december until 2016 May. Next month I need 2016 January till 2016 June.
I need this report monthly so the tickboxes are not going to do much for me unfortunately. I tried doing this with syntaxes but I have no clue how to do this @ a lot of errors.
Posting date has the date I need. This is connected to a field called Material within the same table(table 1). Material is connected to Material# in (master) table 2. Then I have table3 which has Material too and is connected to (master) table 2 as well.
Hope this helps and someone is actually willing to help this noob :).
Thanks!
P.S: Cannot change the source files. Hence I need it per month.
@Jerryvb You'll need a Calendar Table for this. Do you have one?
If not create one - follow this example - http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
Then it will be pretty straightforward...
This is your Running Total for the Trailing 6 Months ONLY
RT Last 6 Months Sales = CALCULATE ( [Total Sales], DATESINPERIOD ( CalendarTable[Date], LASTDATE ( CalendarTable[Date] ), -6, MONTH ) )
Thank you that is helpfull. But I need to be able to do this without changing the source files and really need it per month.
@Jerryvb, You can create a return the months different between current day and sales date.
Months = IF(YEAR(TODAY())=YEAR('Date'[CalendarDate]),MONTH(TODAY())-MONTH('Date'[CalendarDate])+1,(YEAR(TODAY())-YEAR('Date'[CalendarDate]))*12+MONTH(TODAY())-MONTH('Date'[CalendarDate])+1)
and then add this calculated column to filter
Regards,
Charlie Liao