cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Re: MoM/YTD Sales in one Measue

Just to give some more clarity,

 

I have the orders, Cancels, Rate for each officer each Month. I need a matrix table which shows the officer on your rows and columns will have the calender date.

 

Now I also need a YTD for the selected Month.

YTD.PNG

Super User IV
Super User IV

Re: Daily/MTD/YTD Sales in one column

Hi,

What do you mean by "for the selected month"?  Share a dataset and show the expected result.


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

Re: MoM/YTD Sales in one Measue

Hi,

Share your PBI file.


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

Re: MoM/YTD Sales in one Measue

I created some dummy data to replicate the use case due to company policies I can't share the original .pbix. Please download the attached .pbix file from the below link

 

https://drive.google.com/file/d/1GdoSI2Bg7w_Bi3t3Dtl2PnJ3bWh50a7Y/view?usp=sharing

 

I include some notes in .pbix file

 

Thanks & Regards

Super User IV
Super User IV

Re: MoM/YTD Sales in one Measue

Hi,

You may download the PBI file from here.  I just turned on Column totals under Subtotals in the Format pane

Hope this helps.

Untitled.png


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

Re: MoM/YTD Sales in one Measue

Hi Ashish,

 

This will only give the subtotal but if you have data for 2018, the subtotal will not reflect YTD

 

Thanks & Regards,

Anvesh Soma.

Super User IV
Super User IV

Re: MoM/YTD Sales in one Measue

Hi,

Try this measure

Measure = if(HASONEVALUE(Sheet1[DateField]),SUM(Sheet1[Orders]),CALCULATE(SUM(Sheet1[Orders]),DATESBETWEEN(Sheet1[DateField],DATE(YEAR(MAX(Sheet1[DateField])),1,1),MAX(Sheet1[DateField]))))

Hope this helps.


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

Re: MoM/YTD Sales in one Measue

Hi Ashish,

 

Thanks for providing the measure. The measure works fine but when there is a single selection in the date the total is not carrying the YTD. It only showing the total for that month. Rather it has to show the YTD till that selected month

 

Thanks & Regards,

Anvesh Soma.

Super User IV
Super User IV

Re: MoM/YTD Sales in one Measue

Hi,

Does this work?

Measure = if(HASONEVALUE(Sheet1[DateField]),SUM(Sheet1[Orders]),CALCULATE(SUM(Sheet1[Orders]),DATESBETWEEN(Sheet1[DateField],DATE(YEAR(TODAY()),1,1),MAX(Sheet1[DateField]))))

I have assumed that your FY is Jan-Dec.


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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors