Column Chart - various questions

[Anonymous]

Hi I'm trying to create a visual (Stacked Column chart) to show the data for all the periods up to now and the current period (separate visual)

Data is as follows:

Block | Date | Occupancy

A | 13/09/2019 | 34

A | 20/09/2019 | 36

A | 27/09/2019 | 38

B | 04/10/2019 | 40

B | 11/10/2019 | 44

B | 18/10/2019 | 45

C | 25/10/2019 | (not yet populated)

C | 02/11/2019 | (not yet populated)

A bigger representation of teh sample data is here:

https://pasteboard.co/IAJiUk3.png

The data points are working Friday dates so it's quite irregular as some Fridays fall on holidays, etc. Periods (Blocks) start in September (Block A) and end in July Block F. Please note that although we are in Block A, I've added more sample data up till Block C to help create the visual.

I'm trying to create a  chart visual spanning from Block A to current period.  See the top visual on the image below:

All Blocks

https://pasteboard.co/IAJfHoq.png

- Although I've changed the setting from 'Date Hierarchy' to "General Date" column in the visual Axis setting, it does not display individual dates (but months: Oct 2019, Nov 2019) on the X axis.

- There's a blank gap at the end of December/ Beginning of January (as the data points do not exist for that period). Can it be more evenly spread - this probably is going to be solved with my previous point

Also, I'd like to display the more detailed breakdown for the current Block (bottom visual)

Current Block

https://pasteboard.co/IAJg5ru.png

- again the X Axis data points (dates) seem to be misalign with the columns (And do not show the actual data point (dates specified in the dataset)

- How would I automatically dispaly the latest block? I assume it could be done by checking the last non-empty row in the eg. Occupancy column and getting the block from that. Although all the Friday dates are prepopulated, staff inputs the relevant data each week.

Please advise and thank you very much.

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

You can try the following methods:

1. It does not display individual dates on the X-axis and has blank gaps:

In the Format, change the X-axis type to Categorical, the solution in the Current Block is the same:

2. How to automatically display the latest block:

You can create a measure as follows:

Measure =
VAR _lastdateblock =
    CALCULATE (
        MAX ( 'Table'[Block] ),
        FILTER (
            ALLSELECTED ( 'Table'[Date] ),
            'Table'[Date]
                = CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Occupancy] <> BLANK () )
                )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Occupancy] ),
        FILTER ( 'Table', 'Table'[Block] = _lastdateblock )
    )

When you create a visual, the results are as follows:

Here is a demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ee9YPKNrTaNIinDgPL9epukBIxgUaEuvAIFD1qxX2ZqMhQ?e=CfxnAv

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much for the explanation and demo. That's it. The demo looks great.

I'm now trying to get more confident with DAX.