Mapping 1 column values to date columns in matrix

[klehar]

Hi,

For simplification, striping down the actual problem.

I have 2 datasets that look like this:

Geo Dates budget (Fact Table)

Dates (Dim Table)

Geo: (Dim Table)

I have created a mapping between these tables in model as follows

I have a simple requirement

I want a table like this

	FY21
	Q1	Q2	Q3	Q4
Budget	10	13	14	18	When India is selected from the Geo table
Budget	14	12	11	10	When US is selected from the Geo table
Budget	24	25	25	28	When nothing/all values are selected

I was trying to use inscope or infiltered, but im not getting the results.

Can someone help?

Power BI file link here

[Anonymous]

Hi @klehar ,

I updated your sample pbix file, please find the attachment for the details. 

1. Create 2 calculated columns as below in Geo Date Budget table to get the fiscal year and quarter

Fiscal Year = LEFT('Geo Date Budget'[Fiscal Year Quarter],4)

Quarter = RIGHT('Geo Date Budget'[Fiscal Year Quarter],2)

2. Create a matrix visual (Rows: Geo  Columns: new created calculated columns  Values: sum of Budget)

Best Regards

[klehar]

@Anonymous I want the Year and Quarter on the column shelf coming from the dates table.

[Anonymous · Accepted solution]

Hi @klehar ,

I checked your sample pbix file again, found there was a relationship created between Date table and Geo Date Budget table based on the field [Fiscal Year Quarter]. But as you see, their data format are totally different. So if the relationship exist, the visual will not display any value due to there is no common value....

Date Geo Date Budget

I updated your sample pbix file again, it include the following steps. Please find the details in the attachment.

1. Delete the relationship between Date table and Geo Date Budget table 

2. Update the measure [ARR Budget]

ARR Budget = 
VAR _tab =
    SUMMARIZE (
        'Geo Date Budget',
        'Geo Date Budget'[Geo],
        "budget",
            CALCULATE (
                SUM ( 'Geo Date Budget'[Budget] ),
                FILTER (
                    ALLSELECTED ( 'Geo Date Budget' ),
                    'Geo Date Budget'[Geo] = SELECTEDVALUE ( 'Geo Date Budget'[Geo] )
                        && RIGHT ( 'Geo Date Budget'[Fiscal Year Quarter], 4 )
                            = RIGHT ( SELECTEDVALUE ( 'Date'[Fiscal Year Quarter] ), 4 )
                )
            )
    )
RETURN
    SUMX ( _tab, [budget] )

Measure = SUMX(GROUPBY('Date','Date'[Year],'Date'[Quarter]),[ARR Budget])

3. Create matrix

Best Regards

[klehar]

@Greg_Deckler can you please help?

[amitchandak]

@klehar , I think you need to correct Date table Geotable join. I created new key columns in both tables and joined again. See of attached file can help

[klehar]

@amitchandak not sure if this is the right approach

All quarters will join to the first month in the dataset

Also the moment i pull in any measures in the matrix it stops working

[amitchandak]

@klehar , I think isinscope was the problem in measure. do you need that now in this display?

I tried like

ARR Budget = 
SUM ('Geo Date Budget'[Budget]) 

and it worked.

Also, I usually create dates, even if data is qtr, month, or at year level, and use end date or qtr or month or year.

That allows me to use closingbalancemonth, closingbalancequarter, closingbalanceyear.

That gives the same number across that period.