Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SachinNamdeo-20
Helper II
Helper II

Calculate only quarter sales

"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 quarterinvoice quantity/sales  
01-01-22jan 110  
11-02-22feb120  
21-03-22march130  
01-04-22apr240  
21-05-22may250  
11-01-22june210  
 july320  
 sep3   
 oct3   

 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"

1 ACCEPTED SOLUTION
SachinNamdeo-20
Helper II
Helper II

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

View solution in original post

10 REPLIES 10
SachinNamdeo-20
Helper II
Helper II

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

v-xinruzhu-msft
Community Support
Community Support

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:

vxinruzhumsft_0-1671417314238.png

vxinruzhumsft_1-1671417343976.png

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.

Thanks for the suggestion , but it gives me blank value

 

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)
vxinruzhumsft_0-1671428385598.png

 

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.

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

 

 

 

Mahesh0016
Super User
Super User

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 !! 

"

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"junejunemaymayaprapr
 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 

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:

FreemanZ_2-1671272973178.png

 

FreemanZ_0-1671272926562.png

FreemanZ_1-1671272941627.png

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 

Hi @SachinNamdeo-20 

Google is blocked here. 😂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors