Forum Discussion
Matrix Table: Show selected month and previus months
Hi all,
I have a simple data model with a Calendar table and Sales Table (data and sales)
Taking those tables as a starting point, I want to build a matrix table that when selecting 1 year and 1 month in the slicer, it displays:
- Sales data from the fiscal year starting month (first of July) to the selected month. If there is a month without data -> in blank
- Future months until the fiscal year end (30 of June) -> in blank
I have tried with the following measure, but it doesn't work
Monthy sales =
VAR MonthSelect =
SELECTEDVALUE(Calendar[Fiscal MonthNumber])
RETURN
IF ( VALUES( Calendar[Fiscal MonthNumber]) <= MonthSelect;
CALCULATE ( sum(Sales[Sales] ) );
BLANK()
)https://www.dropbox.com/s/dkzogijvt4e8h0h/Test10.pbix?dl=0
Thank you very much in advance
Reuben
Hi,
Try this measure
=SUMX(SUMMARIZE(VALUES(CalendarUnrelated[Fiscal Month]),CalendarUnrelated[Fiscal Month],"ABCD",[Monthly Sales]),[ABCD])
Hope this helps.
8 Replies
- amitchandak
Super User
I think the problem is that you are trying to display fiscal months and also try to filter on the same. In case you want the month to be displayed even if the filter is clicked then you might need additional date slicer not joined to this table.
- Ashish_Mathur
Hi,
You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hope this helps.
thanks Ashish_Mathur . Could you please share in here the code for the measure [demand charge in past x months]? As I have an older version of power pivot I cannot see it.
Thanks again.
- Ashish_Mathur
Hi,
Please download the workbook (do not open the workbook). Open PowerBI Desktop and go to File > Import > Excel workbook (the first option). All Tables, relationships and measures will not appear in PowerBI Desktop.
