topic Re: Calculate only quarter sales in DAX Commands and Tips

Calculate only quarter sales

SachinNamdeo-20 — Sat, 17 Dec 2022 06:44:27 GMT

"I want to calculate quarter sales and within this measure i also can but , i want some this type of measure if we choose any month of calender then the output give only the quarter value not that monts sales value"

QTD Sales = CALCULATE(SUM(CUBE_INVOICE[INVOICE QUANTITY]),DATESQTD((CUBE_INVOICE[D DATE.FULLDATE])))

as ex table

sample date	month	quarter	invoice quantity/sales
01-01-22	jan	1	10
11-02-22	feb	1	20
21-03-22	march	1	30
01-04-22	apr	2	40
21-05-22	may	2	50
11-01-22	june	2	10
	july	3	20
	sep	3
	oct	3

and i got some this kind of output with this measure:-
if i use this measure this give me march month value =60,
but when i select the jan month it gives me 10 value and my desired output was 60.
Basically i want to say taht i want if i choose any of month of its particular quarter it gives me the same value ,the value will only change when we select other quarter2 month and that value will be real value of quarter 2 sales"

Re: Calculate only quarter sales

Mahesh0016 — Sat, 17 Dec 2022 06:54:37 GMT

create below two measure.

Total invoiceQty = SUM(CUBE_INVOICE[INVOICE QUANTITY])

QTD Sales= TOTALQTD([Total invoiceQty],CUBE_INVOICE[D DATE.FULLDATE])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: Calculate only quarter sales

SachinNamdeo-20 — Sat, 17 Dec 2022 07:20:27 GMT

its also give the same output as previous one, when select last month of qurater it give the correct value but when we select first or second month of quarter it gives monthly sales vlaue
these are some screenshot it will help you to understand that what i want to say"
I want that kind of measure that give me same value as ex june have quarter sales vales is [1.03M] then may and apr give me same value

Re: Calculate only quarter sales

FreemanZ — Sat, 17 Dec 2022 10:32:02 GMT

hi SachinNamdeo-20

your report hides some complexity of the filter context. Let me propose the most independent way:

1) try to add a column with this:

QtrInv = VAR _qtr = FORMAT(tbl[Date], "YYYYQQ") RETURN SUMX( FILTER( tbl, _qtr = FORMAT(tbl[Date], "YYYYQQ") ), tbl[Invoice] )

2) then pull the new column to your card visual, choose average or median, instead of sum by default.

i tried and it worked like this:

Re: Calculate only quarter sales

SachinNamdeo-20 — Sat, 17 Dec 2022 11:35:28 GMT

Thank you for your valuable suggestion , but this measure can not satisfying my condition , i am sharing my pbi file with this if you can help me then im grateful to you

Here is my pbix file link for data view
https://drive.google.com/file/d/1NLSkxwZeiwXCpaMheJRT6kVBLmCOgFhz/view?usp=sharing

Re: Calculate only quarter sales

FreemanZ — Sat, 17 Dec 2022 13:27:52 GMT

Hi SachinNamdeo-20

Google is blocked here. 😂

Re: Calculate only quarter sales

Anonymous — Mon, 19 Dec 2022 02:36:46 GMT

Hi SachinNamdeo-20

You can create a measure like :

Measure = var a=FILTER(ALL('Table'),QUARTER('Table'[sample date ])=QUARTER(SELECTEDVALUE('Table 2'[Date]))&&YEAR('Table'[sample date ])=YEAR(SELECTEDVALUE('Table 2'[Date]))) return SUMX(a,[invoice quantity/sales])

Output:

Best Regards!

Yolo Zhu

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

Re: Calculate only quarter sales

SachinNamdeo-20 — Mon, 19 Dec 2022 04:52:38 GMT

Thanks for the suggestion , but it gives me blank value

Re: Calculate only quarter sales

Anonymous — Mon, 19 Dec 2022 05:39:52 GMT

Hi SachinNamdeo-20

Did you choose the date, it will appear if you choose the date,or you can try this code, i set as "0" when there is no selection

Measure = var a=FILTER(ALL('Table'),QUARTER('Table'[sample date ])=QUARTER(SELECTEDVALUE('Table 2'[Date]))&&YEAR('Table'[sample date ])=YEAR(SELECTEDVALUE('Table 2'[Date])))

return IF(ISFILTERED('Table 2'[Date]),SUMX(a,[invoice quantity/sales]),0)

Best Regards!

Yolo Zhu

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

Re: Calculate only quarter sales

SachinNamdeo-20 — Mon, 19 Dec 2022 06:15:05 GMT

IF i use this it give only 0 value at any selection

Re: Calculate only quarter sales

SachinNamdeo-20 — Mon, 19 Dec 2022 06:49:41 GMT

I have find my solution with this measure

Qtr Sales with no changes = CALCULATE(SUM(CUBE_INVOICE[INVOICE QUANTITY]),DATESQTD(ENDOFQUARTER('CUBE_INVOICE'[D DATE.FULLDATE])))

Thank you every on for your support