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
Anonymous
Not applicable

Daily/MTD/YTD Sales in one column

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)PeriodActualBudget% to BudgetActualBudget% to BudgetAs of Date
GrossGrossGrossNet $Net $Net
$$    
ABCDaily00 00 11/9/2018
ABCMonth-To-Date20,75318,261114%10,6719,934107%11/9/2018
ABCYear-To-Date734,263701,000105%389,404381,219102%11/9/2018
DEFDaily00 00 11/9/2018
DEFMonth-To-Date20,64218,649111%13,76812,383111%11/9/2018
DEFYear-To-Date757,670705,058107%504,070469,314107%11/9/2018
GHIDaily00 00 11/9/2018
GHIMonth-To-Date9,0602,478366%1,101657168%11/9/2018
GHIYear-To-Date464,318333,140139%37,34455,58267%11/9/2018
LMNDaily29543967%24637565%11/9/2018
LMNMonth-To-Date3,9133,534111%3,2583,024108%11/9/2018
LMNYear-To-Date135,964112,626121%114,33595,956119%11/9/2018
OPQDaily03610%02720%11/9/2018
OPQMonth-To-Date5,0343,180158%3,7252,390156%11/9/2018
OPQYear-To-Date42,11020,828202%30,98314,351216%11/9/2018
RSTDaily0230%020%11/9/2018
RSTMonth-To-Date2001,62712%5313739%11/9/2018
RSTYear-To-Date25,38243,68458%8,6357,411117%11/9/2018
WXYDaily00 00 11/9/2018
WXYMonth-To-Date19455035%4115527%11/9/2018
WXYYear-To-Date29,97030,119100%9,0969,080100%11/9/2018

 

Sales table is standard with a calendar table marked as data table. 

 

 

 

Any help is appreciated - Power BI gurus ??

 

 

Regards

SK

18 REPLIES 18
Anonymous
Not applicable

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

Hi,

Share your PBI file.


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

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.

Untitled.png


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

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.


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

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.


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

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.


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

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.


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

BrandPeriodActual GrossBudget Gross% to Budget GrossActual NetBudget Net% to Budget NetAs of Date
         
ABCDaily00 00 11/9/2018
ABCMonth-To-Date20,75318,261114%10,6719,934107%11/9/2018
ABCYear-To-Date734,263701,000105%389,404381,219102%11/9/2018
DEFDaily00 00 11/9/2018
DEFMonth-To-Date20,64218,649111%13,76812,383111%11/9/2018
DEFYear-To-Date757,670705,058107%504,070469,314107%11/9/2018
GHIDaily00 00 11/9/2018
GHIMonth-To-Date9,0602,478366%1,101657168%11/9/2018
GHIYear-To-Date464,318333,140139%37,34455,58267%11/9/2018
LMNDaily29543967%24637565%11/9/2018
LMNMonth-To-Date3,9133,534111%3,2583,024108%11/9/2018
LMNYear-To-Date135,964112,626121%114,33595,956119%11/9/2018
OPQDaily03610%02720%11/9/2018
OPQMonth-To-Date5,0343,180158%3,7252,390156%11/9/2018
OPQYear-To-Date42,11020,828202%30,98314,351216%11/9/2018
RSTDaily0230%020%11/9/2018
RSTMonth-To-Date2001,62712%5313739%11/9/2018
RSTYear-To-Date25,38243,68458%8,6357,411117%11/9/2018
WXYDaily00 00 11/9/2018
WXYMonth-To-Date19455035%4115527%11/9/2018
WXYYear-To-Date29,97030,119100%9,0969,080100%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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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]

3.PNG

 

BTW, budget table has less records so this matrix not show so many corresponed records.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.