Filter on slicer but select whole month

[Joe_100]

Hi all,

Please see the table below

Period	Sales	Budget	Month	Year	HalfPeriod
012018P1	10	15	1	2018	1
012018P2	15	20	1	2018	2
022018P1	14	22	2	2018	1
022018P2	14	13	2	2018	2
032018P1	13	15	3	2018	1
032018P2	21	15	3	2018	2
042018P1	11	10	4	2018	1
042018P2	19	20	4	2018	2

I have a slicer on report period. If i select for example report period 032018P1 i get the full month budget with the following DAX (summing both periods in the month)

Budget Sum =
VAR myperiod =
    SELECTEDVALUE ( Table1[Month ] )
RETURN
    CALCULATE (
        SUM ( Table1[Budget] ),
        Table1[Month ] = myperiod,
        ALL ( Table1[Period] )
    )

 Now i have to split the budget in Period 1 and Period 2, even if i select only one period in the month.

So i had the idea to create two measures Period 1 and Period 2 use the same dax as above and then use a filter function to filter on HalfPeriod.

Somehow i can't seem to get it working....How can i add a filter to the dax expression above?

Thanks!

[LivioLanzo]

Try like this:

PeriodOneSales =
CALCULATE (
    SUM ( Sales[Sales] ),
    ALL ( Periods[Period] ),
    VALUES ( Periods[Month ] ),
    VALUES ( Periods[Year] ),
    Periods[HalfPeriod] = 1
)

PeriodTwoSales =
CALCULATE (
    SUM ( Sales[Sales] ),
    ALL ( Periods[Period] ),
    VALUES ( Periods[Month ] ),
    VALUES ( Periods[Year] ),
    Periods[HalfPeriod] = 2
)

[Joe_100 · Accepted solution]

Hi all,

Your advise defenitely helped.

I now created the following DAX and seems to work (two times off course, period 1 and 2)

Thanks!

Period2 =
VAR myperiod =
SELECTEDVALUE (Table1[Month ])
var myyear =
SELECTEDVALUE(Table1[Year])
RETURN
CALCULATE (
SUM ( Table1[Budget]);
Table1[Month ] = myperiod;
Table1[Year] = myyear;
ALL ( Table1[Period] );
Table1[HalfPeriod] = 2)

[Greg_Deckler]

Hmm, not sure why the ALL clause exists but I would think that it should be:

Budget Sum P1 =
VAR myperiod =
    SELECTEDVALUE ( Table1[Month ] )
RETURN
    CALCULATE (
        SUM ( Table1[Budget] ),
        Table1[Month ] = myperiod && Table1[HalfPeriod]=1
    )


Budget Sum P2 =
VAR myperiod =
    SELECTEDVALUE ( Table1[Month ] )
RETURN
    CALCULATE (
        SUM ( Table1[Budget] ),
        Table1[Month ] = myperiod && Table1[HalfPeriod]=2
    )