cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
mogunase1
Helper III
Helper III

WTD with respect to Calculated measure

Hi,

I want to calculate WTD with respect to measure value.

Here,Submissions Volume is a Calculated measure with combination other two Tables. 

Date Field is coming from Date Table.

I tried this,

Submission Volume WTD =CALCULATE( Submission[Submissions Volume], FILTER( ALL('Date'), 'Date'[Week-End_Date] <= MAX('Date'[Week-End_Date]) ) )
But not working,PFB.

mogunase1_1-1684618997571.png

I need Answer like below, Which is consolidated count of Submission Volume Measure with respect to week Wise.

mogunase1_3-1684619341338.png

I have Uploaded the Sample PBIX file in below drive, if possible work on that file and help me with this dax.

 

https://drive.google.com/file/d/1MEnTlniULSJmMA5WZxMJgyG6ceHRRzPf/view?usp=share_link

 

Thanks,

Mohanraj

 

@Greg_Deckler @amitchandak 

 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

pls try this

Submission Volume WTD = 
VAR _t1 =
    ADDCOLUMNS (  GENERATE('Date',VALUES(Submission[SUBMISSION_STATUS])), "@Submissions Volume", [Submissions Volume] )
RETURN SUMX(VALUES('Date'[Week-End_Date]),
    SUMX ( _t1, [@Submissions Volume] ) )

 ======
Submission Volume WTD Fixed = CALCULATE([Submission Volume WTD],REMOVEFILTERS('Date'[Date]),REMOVEFILTERS(Submission[SUBMISSION_STATUS]))

 

Screen Capture #1164.png 

View solution in original post

5 REPLIES 5
Ahmedx
Super User
Super User

pls try this

Submission Volume WTD = 
VAR _t1 =
    ADDCOLUMNS (  GENERATE('Date',VALUES(Submission[SUBMISSION_STATUS])), "@Submissions Volume", [Submissions Volume] )
RETURN SUMX(VALUES('Date'[Week-End_Date]),
    SUMX ( _t1, [@Submissions Volume] ) )

 ======
Submission Volume WTD Fixed = CALCULATE([Submission Volume WTD],REMOVEFILTERS('Date'[Date]),REMOVEFILTERS(Submission[SUBMISSION_STATUS]))

 

Screen Capture #1164.png 

Hi @Ahmedx 

 

Thanks a lot, Working Perfect!

 

 

mogunase1
Helper III
Helper III

Hi Ahmed,

Thanks for your reply!

 

For the same file which you uploaded in drive, If i Use Submission_Status and Weekend Date column alone, It is not working.

mogunase1_0-1684917689252.png

In My scenario i need to show Weekend and Submission Status ,like below. 

mogunase1_1-1684917860761.png

 

After that i need to do Divide(Submission Volume with Submission Volume WTD Fixed)

End Result will be like below,

mogunase1_2-1684918168589.png

 

Thanks,

Mohanraj

@Ahmedx @Ashish_Mathur 

 

 

Ahmedx
Super User
Super User

you need to write two measures like this:
Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiCWojXTMORz-fJ-_?e=8UJXNw

Screen Capture #1136.pngScreen Capture #1139.png

Ashish_Mathur
Super User
Super User

Hi,

The answer should be 12 in the first 3 rows (not 13) probably becase an ID is repeating.  Anyways, try this measure for a start

Measure = CALCULATE([Submissions Volume],DATESBETWEEN('Date'[Date],Max('Date'[Week-End_Date])-7,Max('Date'[Week-End_Date])))

hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors