Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need to display Daily Sales , MTD Sales and YTD Sales in one column as shown below. I have used the DAX MTD, YTD functions but am not able to display as shown below in Power BI.
Any suggestions ? Client is using Quick base and able to produce this report ( Note, Actual Gross $ is one column)
Brand ($000s) | Period | Actual | Budget | % to Budget | Actual | Budget | % to Budget | As of Date |
Gross | Gross | Gross | Net $ | Net $ | Net | |||
$ | $ | |||||||
ABC | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
ABC | Month-To-Date | 20,753 | 18,261 | 114% | 10,671 | 9,934 | 107% | 11/9/2018 |
ABC | Year-To-Date | 734,263 | 701,000 | 105% | 389,404 | 381,219 | 102% | 11/9/2018 |
DEF | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
DEF | Month-To-Date | 20,642 | 18,649 | 111% | 13,768 | 12,383 | 111% | 11/9/2018 |
DEF | Year-To-Date | 757,670 | 705,058 | 107% | 504,070 | 469,314 | 107% | 11/9/2018 |
GHI | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
GHI | Month-To-Date | 9,060 | 2,478 | 366% | 1,101 | 657 | 168% | 11/9/2018 |
GHI | Year-To-Date | 464,318 | 333,140 | 139% | 37,344 | 55,582 | 67% | 11/9/2018 |
LMN | Daily | 295 | 439 | 67% | 246 | 375 | 65% | 11/9/2018 |
LMN | Month-To-Date | 3,913 | 3,534 | 111% | 3,258 | 3,024 | 108% | 11/9/2018 |
LMN | Year-To-Date | 135,964 | 112,626 | 121% | 114,335 | 95,956 | 119% | 11/9/2018 |
OPQ | Daily | 0 | 361 | 0% | 0 | 272 | 0% | 11/9/2018 |
OPQ | Month-To-Date | 5,034 | 3,180 | 158% | 3,725 | 2,390 | 156% | 11/9/2018 |
OPQ | Year-To-Date | 42,110 | 20,828 | 202% | 30,983 | 14,351 | 216% | 11/9/2018 |
RST | Daily | 0 | 23 | 0% | 0 | 2 | 0% | 11/9/2018 |
RST | Month-To-Date | 200 | 1,627 | 12% | 53 | 137 | 39% | 11/9/2018 |
RST | Year-To-Date | 25,382 | 43,684 | 58% | 8,635 | 7,411 | 117% | 11/9/2018 |
WXY | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
WXY | Month-To-Date | 194 | 550 | 35% | 41 | 155 | 27% | 11/9/2018 |
WXY | Year-To-Date | 29,970 | 30,119 | 100% | 9,096 | 9,080 | 100% | 11/9/2018 |
Sales table is standard with a calendar table marked as data table.
Any help is appreciated - Power BI gurus ??
Regards
SK
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.
Hi,
Share your PBI file.
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
Hi,
You may download the PBI file from here. I just turned on Column totals under Subtotals in the Format pane
Hope this helps.
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.
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.
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.
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.
Hello,
Can you please help me achive this. I need the YTD in the same column like below screnshot
JAN FEB MAY MAR APR YTD
Officer 10 11 12 13 14 60
Please help me
Hi,
Drag Month from the Calendar Table and Designation from the Data Table. Write this measure
=SUM(Data[Amount])
Hope this helps.
Hello Ashish,
But how to get the YTD at the end? I need a single YTD for the selected month at the end?
Hi,
What do you mean by "for the selected month"? Share a dataset and show the expected result.
Brand | Period | Actual Gross | Budget Gross | % to Budget Gross | Actual Net | Budget Net | % to Budget Net | As of Date |
ABC | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
ABC | Month-To-Date | 20,753 | 18,261 | 114% | 10,671 | 9,934 | 107% | 11/9/2018 |
ABC | Year-To-Date | 734,263 | 701,000 | 105% | 389,404 | 381,219 | 102% | 11/9/2018 |
DEF | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
DEF | Month-To-Date | 20,642 | 18,649 | 111% | 13,768 | 12,383 | 111% | 11/9/2018 |
DEF | Year-To-Date | 757,670 | 705,058 | 107% | 504,070 | 469,314 | 107% | 11/9/2018 |
GHI | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
GHI | Month-To-Date | 9,060 | 2,478 | 366% | 1,101 | 657 | 168% | 11/9/2018 |
GHI | Year-To-Date | 464,318 | 333,140 | 139% | 37,344 | 55,582 | 67% | 11/9/2018 |
LMN | Daily | 295 | 439 | 67% | 246 | 375 | 65% | 11/9/2018 |
LMN | Month-To-Date | 3,913 | 3,534 | 111% | 3,258 | 3,024 | 108% | 11/9/2018 |
LMN | Year-To-Date | 135,964 | 112,626 | 121% | 114,335 | 95,956 | 119% | 11/9/2018 |
OPQ | Daily | 0 | 361 | 0% | 0 | 272 | 0% | 11/9/2018 |
OPQ | Month-To-Date | 5,034 | 3,180 | 158% | 3,725 | 2,390 | 156% | 11/9/2018 |
OPQ | Year-To-Date | 42,110 | 20,828 | 202% | 30,983 | 14,351 | 216% | 11/9/2018 |
RST | Daily | 0 | 23 | 0% | 0 | 2 | 0% | 11/9/2018 |
RST | Month-To-Date | 200 | 1,627 | 12% | 53 | 137 | 39% | 11/9/2018 |
RST | Year-To-Date | 25,382 | 43,684 | 58% | 8,635 | 7,411 | 117% | 11/9/2018 |
WXY | Daily | 0 | 0 | 0 | 0 | 11/9/2018 | ||
WXY | Month-To-Date | 194 | 550 | 35% | 41 | 155 | 27% | 11/9/2018 |
WXY | Year-To-Date | 29,970 | 30,119 | 100% | 9,096 | 9,080 | 100% | 11/9/2018 |
Output shown above
HI @Anonymous,
You can create new table with period category, write a measure to check current row brand and period, then use switch function to change calculate formula based on current period and brand.
Finally, you can create a matrix visual with brand and period to row, column fields , measure to value field.
If you still confused to coding formula, please share a pbix file with some sample data for test.
Regards,
Xiaoxin Sheng
Xiaoxin Sheng,
I sort of get it but would like to see the code implemented to get the exact results as shown in screen.
What is your email address ? I will email you a sample .pbix file.
Thanks
SK
Hi @Anonymous,
You can upload sample file to onedrive or google drive then share link here.
Notice: do mask on sensitive data before sharing.
Regards,
Xiaoxin Sheng
https://drive.google.com/file/d/1bkT69njeL_lT3Klp-AornQhmOkBuOGp8/view?usp=sharing
Note, I also want the ability to drill through to another Page in Power BI that has detail SKU data ( the detail SKU data is NOT in the file and I will prepare it and share the link tomorrow)
Hi @Anonymous,
You can create a table with date period unit labels, then use it to create matrix with below measures.
Table formula:
Period = DATATABLE("Type",STRING,{{"Daily"},{"MTD"},{"YTD"}})
Measures:
Dynamic Actual = VAR currDate = MAX ( Calendar[Date] ) RETURN SWITCH ( SELECTEDVALUE ( 'Period'[Type] ), "Daily", CALCULATE ( SUM ( DailySales[Actual Gross Daily] ), VALUES ( BrandName[Brand] ), VALUES ( 'Calendar'[Date] ) ), "MTD", CALCULATE ( SUM ( DailySales[Actual Gross Daily] ), FILTER ( ALLSELECTED ( DailySales ), [GL Date] <= currDate ), VALUES ( BrandName[Brand] ), VALUES ( 'Calendar'[Month] ), VALUES ( 'Calendar'[Year] ) ), "YTD", CALCULATE ( SUM ( DailySales[Actual Gross Daily] ), FILTER ( ALLSELECTED ( DailySales ), [GL Date] <= currDate ), VALUES ( BrandName[Brand] ), VALUES ( 'Calendar'[Year] ) ), BLANK () ) Dynamic Budget = VAR currDate = MAX ( Calendar[Date] ) RETURN SWITCH ( SELECTEDVALUE ( 'Period'[Type] ), "Daily", CALCULATE ( SUM ( Budget[Gross Sales Budget] ), VALUES ( BrandName[Brand] ), VALUES ( 'Calendar'[Date] ) ), "MTD", CALCULATE ( SUM ( Budget[Gross Sales Budget] ), FILTER ( ALLSELECTED ( Budget ), [Date] <= currDate), VALUES ( BrandName[Brand] ), VALUES ( 'Calendar'[Month] ), VALUES ( 'Calendar'[Year] ) ), "YTD", CALCULATE ( SUM ( Budget[Gross Sales Budget] ), FILTER ( ALLSELECTED ( Budget ), [Date] <= currDate ), VALUES ( BrandName[Brand] ), VALUES ( 'Calendar'[Year] ) ), BLANK () ) % to Budget = [Dynamic Actual]/[Dynamic Budget]
BTW, budget table has less records so this matrix not show so many corresponed records.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |