Budget for whole months if day selected

[Analitika]

I have budget for each month, but need to see each day fact, so i need in visual chart show budget whole month and fact by day, but if i select days budget do not show at all

Selected whole Month, it ok

Selected day, it is bad, as only facts left

Budget =

VAR _yIndex =

MAX ( 'Date'[Year] )

VAR _mIndex =

MAX ( 'Date'[Month] )

VAR _calc =

CALCULATE ( [BudgetTbl], (BudgetTbl[year]) = _yIndex, (BudgetTbl[Month]) = _mIndex )

RETURN

_calc

------------------------------

Fact =

VAR _yIndex =

MAX ( 'Date'[Year] )

VAR _mIndex =

MAX ( 'Date'[Month] )

VAR _calc =

CALCULATE (

[FactTbl],

YEAR ( 'FactTbl'[date] ) = _yIndex

&& MONTH ( 'FactTbl'[date] ) = _mIndex

)

RETURN

_calc

[joanwanjiru]

Budget =

 

VAR Min_Date = STARTOFMONTH('Calendar'[Date])

 

VAR end_month = ENDOFMONTH('Calendar'[Date])

 

RETURN

CALCULATE(

    SUM(budget[Value]),

   DATESBETWEEN('Calendar'[Date], Min_Date, end_month))

This could also help 

[v-yiruan-msft]

Hi @Analitika ,

As I mocked some sample data and create the column chart using your measures, I can't reproduce the problem(see below screenshot). Could you please provide some sample data in BudgetTbl and FactTbl table? And how did you set your column chart? Thank you.

Best Regards

[Analitika]

@v-yiruan-msft  Did you select day?

[v-yiruan-msft]

Hi @Analitika ,

Both budget and fact values can display in column chart when I select the day as shown in the below screenshot. By the way, the date in X axis of my column chart is from Date field of table Date...

Best Regards

[Analitika]

@v-yiruan-msft 
Did your fact table have each day values? My have each day

Did your budget table have each day values? My have only whole month
Could you please share your file?

[v-yiruan-msft]

Hi @Analitika ,

The attachment is my sample pbix file, you can check whether there is any difference with yours.

Best Regards

[Analitika]

@v-yiruan-msft  You dont have relations between dates, add them and you will see a problem

[v-yiruan-msft]

Hi @Analitika ,

Please try to update the formula of measure as below and check whether it can get your desired result:

Budget =
VAR _yIndex =
    MAX ( 'Date'[Year] )
VAR _mIndex =
    MAX ( 'Date'[Month] )
VAR _calc =
    CALCULATE (
        SUM ( 'BudgetTbl'[Budgets] ),
         ( BudgetTbl[year] ) = _yIndex,
         ( BudgetTbl[Month] ) = _mIndex,
        ALL ( 'Date'[Date] )
    )
RETURN
    _calc

Fact =
VAR _yIndex =
    MAX ( 'Date'[Year] )
VAR _mIndex =
    MAX ( 'Date'[Month] )
VAR _calc =
    CALCULATE (
        SUM ( 'FactTbl'[Facts] ),
        YEAR ( 'FactTbl'[date] ) = _yIndex
            && MONTH ( 'FactTbl'[date] ) = _mIndex,
        ALL ( 'Date'[Date] )
    )
RETURN
    _calc

Best Regards

[Analitika · Accepted solution]

Not work, i solved problem to set all budgets to start of each month instead of eom, an related date table to it