Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Geniuses,
I want to create a chart like below
Client | Jan | Feb | Mar |
---|---|---|---|
Sony | 5000 | 5020 | 5040 |
Samsung | 6000 | 6030 | 6050 |
I have a data from the year 2010 to 2016, what client wants is he want to see the data starting from Jan 2016 so now what he wants is that all the data in the year 2010 to 2015 will combine together and will be shown in Jan and then Feb will be the cumulative sum of Jan and Feb
and Mar is cumulative sum of jan feb mar
the challenge is how to combine whole data of 2010 to 2015 irrespective of any month into the Jan 2016 month
when I say data it is the count of records.
Please help me in this
Thanks,
S
Hi,
You need to create a month column ('Jan','Feb','Mar' etc) in your calendar table. and then use that Month column as dimension in your pivot table. and in expression you can simply use count(Fact).
For better resolution post some dummy data..
HTH
Sushil
I am considering that you have InvoiceDate in your data model.
Create a InvoiceMonthYear in your master calendar or in the same table where InvoiceDate is lying.
Date(MonthStart(TempDate),'MMM-YYYY') as InvoiceMonthYear
Now Create a Pivot Table
Dimension
1) Client
2) =If(InvoiceMonthYear >= YearStart(Today()), InvoiceMonthYear)
Tick Suppress When Value is Null
Expression
RangeSum(Before(IF(InvoiceMonthYear = Date(YearStart(Today()),'MMM-YYYY'), SUM(TOTAL <Customer> {<InvoiceMonthYear = {">=$(=Date(Min(InvoiceMonthYear),'MMM-YYYY'))<=$(=Date(YearStart(Today()),'MMM-YYYY'))"}>}Sales),
SUM({<InvoiceYear = {'$(=Year(Today()))'}>}Sales)), 0, ColumnNo()))
Let me know if this is now working.