Matrix Table: Show selected month and previus months

[Reuben]

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

[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.

[Reuben]

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.

[Reuben]

Hi,

I have create a duplicate Unrelated Calendar and use it to put the months in columns

And then, the following measure:

Monthly Sales = 
var maxDateSelection = MAXX(ALLSELECTED('Calendar');MAX('Calendar'[Fiscal MonthNumber]))
return
IF(MAX('CalendarUnrelated'[Fiscal MonthNumber]) <= maxDateSelection;
    CALCULATE(
        SUM(Sales[Sales]);
        FILTER(All('Calendar');
            'Calendar'[Fiscal MonthNumber] = max('CalendarUnrelated'[Fiscal MonthNumber]) &&
            'Calendar'[Fiscal Year] = max('Calendar'[Fiscal Year])
        )
    )
)

Monthly data is OK but the grand total in column does not display the sum of the months displayed:

test10

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(CalendarUnrelated[Fiscal Month]),CalendarUnrelated[Fiscal Month],"ABCD",[Monthly Sales]),[ABCD])

Hope this helps.

[Reuben]

It helped a lot! thank you very much @Ashish_Mathur 

Great job in your by the way. Its very helpful for those who are learning (like me).

[Ashish_Mathur]

You are welcome.

[amitchandak]

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.