topic Re: Current Quarter to be shown in card in DAX Commands and Tips

Current Quarter to be shown in card

Anonymous — Wed, 03 Jul 2024 11:39:02 GMT

Hello,

I have the data from May 2023 to till date and I need to show the average current Quarter data in the card visual. Data will have the Transaction date, Adoption % and the store list. The report will be refreshed on a daily basis, but it have data 1 week prior to the current week. (If the report is refreshed on 3rd July, the data available in the report is 23th June to 29th June but we consider that as 23rd June data). The requirement is showing recent Quarter data, I used the Current Month formula to show the data it worked perfectly until June 30th, when it refreshed on July 1st it shows no data available.
I need help to show the current Quarter data in the card visual with the available data in the report.
For example, Data available in the report till June 2024 when the data refreshed on 1st July it has to show the current available data (Quarter) i.e April to June 2024.

Note: Data is available from May 2023 to June 2024

Current QTR =

CALCULATE(AVERAGE('Adoption Trend_Dup'[NewAdoption]),YEAR('Adoption Trend_Dup'[TxnDt]) = YEAR(TODAY()),

'Calendar Table'[Quarter] = ROUNDUP(MONTH(TODAY())/3,0))

Current Qtr_Var =

VAR _summarizeTable = ADDCOLUMNS(SUMMARIZE('Adoption Trend_Dup', 'Adoption Trend_Dup'[TxnDt],"average",AVERAGE('Adoption Trend_Dup'[NewAdoption])), "Quarter", QUARTER('Adoption Trend_Dup'[TxnDt]),"month",MONTH('Adoption Trend_Dup'[TxnDt]),"year",YEAR('Adoption Trend_Dup'[TxnDt]))

VAR _maxMonthNumber = MONTH(MAX('Adoption Trend_Dup'[TxnDt]))

Var _MaxQuarter = QUARTER(MAX('Adoption Trend_Dup'[TxnDt]))

VAR _maxYearNumber = YEAR(MAX('Adoption Trend_Dup'[TxnDt]))

VAR _rowscount = COUNTROWS(FILTER(_summarizeTable,[Quarter] = _MaxQuarter && [year] = _maxYearNumber))

VAR _averageOfNewAdoption = SUMX(FILTER(_summarizeTable,[Quarter] = _MaxQuarter && [year] = _maxYearNumber),[average])

RETURN DIVIDE(_averageOfNewAdoption,_rowscount,0)

StoreName	StoreNo	Txn Dt	New Adoption
AA	11	7th April 2024	10
BB	22	7th April 2024	20
CC	33	7th April 2024	30
AA	11	14th April 2024	50
BB	22	14th April 2024	10
CC	33	14th April 2024	25
AA	11	21th April 2024	100
BB	22	21th April 2024	95
CC	33	21th April 2024	36
AA	11	24th April 2024	55
BB	22	24th April 2024	44
AA	11	5th May 2024	10
BB	22	5th May 2024	20
CC	33	5th May 2024	30
AA	11	12th May 2024	50
BB	22	12th May 2024	10
CC	33	12th May 2024	25
AA	11	19th May 2024	100
BB	22	19th May 2024	95
CC	33	19th May 2024	36
AA	11	26th May 2024	55
BB	22	26th May 2024	66
CC	33	26th May 2024	100
AA	11	2nd June 2024	10
BB	22	2nd June 2024	20
CC	33	2nd June 2024	30
AA	11	9th June 2024	50
BB	22	9th June 2024	10
CC	33	9th June 2024	25
AA	11	16th June 2024	100
BB	22	16th June 2024	95
CC	33	16th June 2024	36
AA	11	23rd June 2024	100
BB	22	23rd June 2024	100
CC	33	23rd June 2024	100

Re: Current Quarter to be shown in card

Anonymous — Thu, 04 Jul 2024 03:22:07 GMT

Hi Anonymous

"I used the Current Month formula to show the data it worked perfectly until June 30th, when it refreshed on July 1st it shows no data available." Does this statement mean that after a refresh, there is no data in the table that should be refreshed?

To better help you, I have a question I need to confirm with you, is the Txn Dt column also formatted like this in your raw data? Because I tried to restore your scenario with your formula, but it produces an error because the Txn Dt column is a text column.

The following is testing I conducted after modifying the Txn Dt column and adding the data for March, for your reference:

1. Create a measure and put the measure into the filter so that the visual only shows data where the measure is equal to 1. Doing this will cause the visualization to only show data for the current quarter.

Measure = VAR _maxQuarter = CALCULATE(QUARTER(MAX([Txn Dt])), ALL('Adoption Trend_Dup')) VAR _1 = IF(QUARTER(MAX([Txn Dt])) = _maxQuarter, 1, 0) RETURN _1

2. Create a measure to calculate the average

average = CALCULATE(AVERAGE('Adoption Trend_Dup'[New Adoption]), FILTER('Adoption Trend_Dup', [Measure] = 1))

Output:

If I misunderstood what you meant, feel free to let me know.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Current Quarter to be shown in card

Anonymous — Thu, 04 Jul 2024 07:12:54 GMT

Hi Anonymous,
Thank you for the reply.
Yes, when the data is refreshed on July 1st, it didn't took the Quarter data. When the data is refreshed on 8th July, it will have to take the from 30th June to 6th July but we will consider whole data as 30th June only. Likewise, if the report refreshed on 15th July, the data in the report will have from 7th to 13th July but the report has to show only July data since we need to consider the Quarter-3 data i.e July to Sep.
Below is the screenshot of Txn Dt, its in Date Format.

When i make the calculation which you have provided, its taking the data of 2023 May and June as well, we need to show only recent quarter in Card.

Re: Current Quarter to be shown in card

Anonymous — Fri, 05 Jul 2024 05:47:35 GMT

Hi Anonymous

Request your help on this.

Re: Current Quarter to be shown in card

Anonymous — Fri, 05 Jul 2024 07:05:33 GMT

Hi Anonymous

I need to confirm your desired result with you. For example, when refreshing on July 15, the data of the previous week will be refreshed to Power BI. The card needs to show the average value of the quarter on July 7? But it is only July now, so only the average value of July is shown?

Best Regards,
Yulia Xu

Re: Current Quarter to be shown in card

Anonymous — Fri, 05 Jul 2024 07:20:31 GMT

Hi Anonymous ,

Thats Correct, this should show the current quarter(July to Sep) which is July Month.

Re: Current Quarter to be shown in card

Anonymous — Mon, 08 Jul 2024 05:23:23 GMT

Hi Anonymous ,

Good day!!!

Request your help on this issue.

Re: Current Quarter to be shown in card

Anonymous — Mon, 08 Jul 2024 07:27:52 GMT

Hi Anonymous

Today is July 8, and the data in the table should be from June 30 to July 6. So should we calculate the average value for the second quarter?

If so, you can try changing the formula to the following:

average = VAR _minQuarter = CALCULATE(QUARTER(MIN([Txn Dt])), ALL('Adoption Trend_Dup')) RETURN CALCULATE(AVERAGE('Adoption Trend_Dup'[New Adoption]), FILTER('Adoption Trend_Dup', QUARTER([Txn Dt]) = _minQuarter))

Since there is no data for other months in the second quarter except June in the table, the above formula only calculates the average value for June.

Output:

If June 30th does not belong to today's quarter and therefore does not need to be calculated, you can try the following method.

average1 = VAR _minQuarter = CALCULATE(QUARTER(MIN([Txn Dt])), ALL('Adoption Trend_Dup')) VAR _currentQuarter = QUARTER(TODAY()) RETURN IF(_minQuarter = _currentQuarter, CALCULATE(AVERAGE('Adoption Trend_Dup'[New Adoption]), FILTER('Adoption Trend_Dup', QUARTER([Txn Dt]) = _minQuarter)), BLANK())

Output:

If the problem persists, feel free to ask me.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Current Quarter to be shown in card

Anonymous — Wed, 10 Jul 2024 11:34:45 GMT

Hi Anonymous

I have updated the 1st Measure(June) in the report, i hope it will work when the next week refresh completed. It will have to show the July Month data, lets see. If not, will let you know.