cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srini_kris Frequent Visitor
Frequent Visitor

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

14 REPLIES 14
srini_kris Frequent Visitor
Frequent Visitor

Re: Daily/MTD/YTD Sales in one column

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

Community Support Team
Community Support Team

Re: Daily/MTD/YTD Sales in one column

HI @srini_kris,

 

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
srini_kris Frequent Visitor
Frequent Visitor

Re: Daily/MTD/YTD Sales in one column

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

Community Support Team
Community Support Team

Re: Daily/MTD/YTD Sales in one column

Hi @srini_kris,

 

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
srini_kris Frequent Visitor
Frequent Visitor

Re: Daily/MTD/YTD Sales in one column

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)

Community Support Team
Community Support Team

Re: Daily/MTD/YTD Sales in one column

Hi @srini_kris,

 

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
anveshsoma1 Frequent Visitor
Frequent Visitor

Re: Daily/MTD/YTD Sales in one column

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

Super User
Super User

Re: Daily/MTD/YTD Sales in one column

Hi,

Drag Month from the Calendar Table and Designation from the Data Table.  Write this measure

=SUM(Data[Amount])

Hope this helps.

anveshsoma1 Frequent Visitor
Frequent Visitor

Re: Daily/MTD/YTD Sales in one column

Hello Ashish,

 

But how to get the YTD at the end? I need a single YTD for the selected month at the end?