The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
I have month, Quarter and year colum. If i select month want to see latest month like July 2017 in text box, If i select Year i want to see like Jan 2017 to July 2017 (we have ten year data) . finally if i select quarter want to see Jan 2017 - mar 2017 (First quarter) also would like to see by quarter wise in text box ..
anyone can help me on this?
Thanks.
Solved! Go to Solution.
@AmalrajRRD1 wrote:
Hi all,
I have month, Quarter and year colum. If i select month want to see latest month like July 2017 in text box, If i select Year i want to see like Jan 2017 to July 2017 (we have ten year data) . finally if i select quarter want to see Jan 2017 - mar 2017 (First quarter) also would like to see by quarter wise in text box ..
anyone can help me on this?
Thanks.
I'd suggest to use a calendar table, when you'd like to show data for 2017, then pick up 2017 in the slicer. when to show data for Q1 2017, pick up Q1 in 2017. May I know why you don't like this way?
As to your requirement, there's some tricky way with complicated measures, the measures values could vary according to the slicer like
value = SWITCH ( LASTNONBLANK ( yourFilter[YMQ], "" ), "Month", CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY ), 1 ) ) ), "Quarter", CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY ), 1 ) && CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY ) + 3, 1 ) ) ) ) ), "Year", CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] >= DATE ( YEAR ( TODAY () ), 1, 1 ) ) ) )
@AmalrajRRD1 wrote:
Hi all,
I have month, Quarter and year colum. If i select month want to see latest month like July 2017 in text box, If i select Year i want to see like Jan 2017 to July 2017 (we have ten year data) . finally if i select quarter want to see Jan 2017 - mar 2017 (First quarter) also would like to see by quarter wise in text box ..
anyone can help me on this?
Thanks.
I'd suggest to use a calendar table, when you'd like to show data for 2017, then pick up 2017 in the slicer. when to show data for Q1 2017, pick up Q1 in 2017. May I know why you don't like this way?
As to your requirement, there's some tricky way with complicated measures, the measures values could vary according to the slicer like
value = SWITCH ( LASTNONBLANK ( yourFilter[YMQ], "" ), "Month", CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY ), 1 ) ) ), "Quarter", CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY ), 1 ) && CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY ) + 3, 1 ) ) ) ) ), "Year", CALCULATE ( SUM ( yourTable[col] ), FILTER ( yourTable, yourTable[date] >= DATE ( YEAR ( TODAY () ), 1, 1 ) ) ) )
User | Count |
---|---|
109 | |
82 | |
74 | |
53 | |
49 |
User | Count |
---|---|
133 | |
97 | |
94 | |
71 | |
69 |