Disable aggregate

[Thiyagu]

Hi All,

I have two tabs, first tab is full of slicers and second tab works based on slicer selection in first tab.

I have some slicers in tab 1, mainly fiscal period and periodic (MTD,QTD,YTD).

I have a Matrix visual in second tab. If i select particular month from Month slicer (Ex : 2017006) and YTD in periodic slicer, i just want to show 2017001----2017006 in matrix visual.

In Matrix visual i'm showing CY Sales and LY Sales. For the above selection, currently matrix visual aggregate 2017001 to 2017006 data. How to show individual month data based on slicer selection.

Thanks,

Thiyaga 

@Baskar @v-jiascu-msft @v-yuta-msft @Anonymous

[v-jiascu-msft]

Hi @Thiyagu,

There could be a trick of independent slicer table. Please check out the demo in the attachment. 

1. Create a new table as a slicer. Don't establish any relationship.

2. Create a measure like this.

Measure =
CALCULATE (
    SUM ( 'Fact'[Value] ),
    FILTER (
        'Calendar',
        'Calendar'[Date] <= MAX ( 'Slicer'[Date] )
            && 'Calendar'[Date] >= FIRSTDATE ( DATEADD ( Slicer[Date], -5, MONTH ) )
    )
)

Best Regards,

Dale

[Anonymous]

Create one more dimension for your Periodic filter and it has values fromdate and enddate. This should be calculated automatically based on your time Dimension and you can use this as your slicer in your reports.

[Anonymous]

Hi Thiagu,

This can be possible in defining your MTD, QTD and YTD in Dimension level. Also, when you return values as you defined in your measure, the return values should be as MTD,QTD or YTD. 

[v-jiascu-msft]

Hi @Thiyagu,

Can you share your pbix file please? Please mask the private parts first. 

Generally, if you select 2017006, only the data of 2017006 will be showed up. Or how can we call it slicer? But we can make some changes in the model. Maybe a measure also could be helpful.

Best Regards,

Dale

[Thiyagu]

Hi @v-jiascu-msft

I agree with you. But users requirement like this. I cant share pbix file, but i can help you with more snip/examples if you want.

My measure is like this,

_Measure = 

Var _curMonth= IF(Hasonefilter(fiscalperiod),values(fiscalperiod),Max(fiscalperiod))

Var _curQtr = IF(Hasonefilter(fiscalquarter),values(fiscalquarter),Max(fiscalquarter))

Var _curYear = IF(Hasonefilter(fiscalyear),values(fiscalyear),Max(fiscalyear))

Var _Period = IF(Hasonefilter(periodic),values(periodic),"MTD")
Var _Scale = IF(Hasonefilter(scale),values(scale),"Whole Number")

Var _Value = SWITCH (
TRUE (),
_Period = "QTD", CALCULATE (
SUM ( actuals),
FILTER (
ALL (
table[Fiscal Period],
table[Fiscal Quarter],
table[Fiscal Year]
),
table[Fiscal Quarter] = _curQtr
&& table[Fiscal Year] = _curYear
&& table[Fiscal Period] <= _curMonth && table[category] = "Sales"
)
),
_Period = "YTD", CALCULATE (
SUM ( table[actuals] ),
FILTER (
ALL (
table[Fiscal Period],
table[Fiscal Quarter],
table[Fiscal Year],table[category]
),
table[Fiscal Year] = _curYear
&& table[Fiscal Period] <= _curMonth && table[category] = "Sales"
)
),
_Period = "MTD", CALCULATE (
SUM ( table[actuals] ),
FILTER (
table,
table[Fiscal Period] = _curMonth && table[category] = "Sales"
)
)
)

Return

SWITCH (
TRUE (),
_Scale = "Whole Number", _Value,
_Scale = "Thousands", DIVIDE ( _Value, 1000 ),
_Scale = "Millions", DIVIDE ( _Value, 1000000 )
)

Thanks,

Thiyaga