Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply

working day, total working day, running total

Below is the link to powerbi file. i need a big help from you..geniuses

Need following columns or measures

 

ColumnMT= MTD running shipped_amount
Working day (for example for Feb 1 2016, working day is 1, for Feb 8, 2016 it is 6 )
ColumnSH = month to date running shipped amount / working day
ColumnRR = ColumnAAC * Total no of working Days for that selected Year and Month

My Line Chart Axis=working Days, values are columnSH, ColumnRR

How can i achieve this? i have been strugling for a week. When i try to create columns, some function doesn't work. When i try to create measure, i am not able to use some calculated columns.

Please Help!!!

 

https://drive.google.com/open?id=0BzUkkp0vBBKiSHp0VVFHY1FYbFE

1 ACCEPTED SOLUTION

Here. Check the updated file with added measures etc.

https://drive.google.com/open?id=0B-p4nPyEKzR1X05uVTV4bW9USUk

 

I'm still not certain what you want to show in the chart.

 

I'd suggest you go through each line carefully and verify that calculated values are what you wanted. If not, you'll want to give at least few samples so that either I or anyone else helping you can verify their solution.

View solution in original post

16 REPLIES 16
Chihiro
Solution Sage
Solution Sage

I'd recommend you add date dimention table.

 

Add new table with formula.

DimDate =
CALENDAR (
    DATE ( YEAR ( MINX ( Table1, Table1[transaction_date] ) ), 1, 1 ),
    DATE ( YEAR ( MAXX ( Table1, Table1[transaction_date] ) ), 12, 31 )
)

Add calculated column to DimDate

Month = MONTH([Date])
Workday = IF(WEEKDAY([Date],2)>5,0,1)

Then add following measures to Table1:

ColumnMT =
CALCULATE (
    SUM ( Table1[shipped_amount] ),
    FILTER (
        ALLSELECTED ( DimDate[Date].[Day] ),
        DimDate[Date].[Day] <= MAX ( DimDate[Date].[Day] )
    )
)
WorkingDays =
CALCULATE (
    SUM ( DimDate[Workday] ),
    FILTER (
        ALLSELECTED ( DimDate[Date].[Day] ),
        DimDate[Date].[Day] <= MAX ( DimDate[Date].[Day] )
    )
)
ColumnSH = [ColumnMT]/[WorkingDays]

However, I'm not sure what you mean by rest of your questions. I was not able to find ColumnAAC in your sample and can't understand how you want to present the data (if you need working days as axis, you'd need it not as measure but as calculated column).

6-15-2017 2-42-07 PM.jpg

 

 

 

You are really a genius 🙂
Sorry, That ColumnACC is ColumnSH

Now for ColumnRR i need to multiply ColumnSH to total no of working days for that month.
Please help me how to do this.

Yes i need working days as axis. how do i create calculated column of working days?

 

🙂

I'm headed out for the night. I'll see if I can find the time tomorrow.

Thank you!

Not sure if this is what you wanted. I'm having trouble understanding purpose of Column RR.

At any rate, check the file in link.

0.JPG

https://drive.google.com/open?id=0B-p4nPyEKzR1V2k3M3k1djY0VUk

WOW..you are very close. One last thing is working days. Working days is considered as Monday to Friday

Today is June 16, 2017, Day =16 but Working Day = 12.  And I am looking working days by month. Is it too much?

 

Other than that, the solution looks great.

I really appreciate your help.

Hmm? Working days should adjust to each month. Remove filter from the visiual and you should see it.

 

If working days should be calculated regardless of filter. Instead of using the measure, use the calculated column added to DimDate table [RWkday].

 

1.JPG

This is what i am getting. Am i doing something wrong?

TestColumn = [ColumnMT]/RELATED(DimDate[RWkday])

cap1.JPG

 

 

 

 

You shouldn't devide measure by column. You should create another measure to devide it by.

 

I'm bit busy right now with work. I'll try to look at later it if you haven't solved it by then.

Sure..i will wait for you. I have no idea how to make it happen. 😞

Here. Check the updated file with added measures etc.

https://drive.google.com/open?id=0B-p4nPyEKzR1X05uVTV4bW9USUk

 

I'm still not certain what you want to show in the chart.

 

I'd suggest you go through each line carefully and verify that calculated values are what you wanted. If not, you'll want to give at least few samples so that either I or anyone else helping you can verify their solution.

One thing i noticed that the numbers in the chart do not match.  It works when you put date (day) in the Axis. 

That's because you wanted to chart based on Working days.

 

Since it's just integer and each month there could be duplicate...

 

I suggest you use actual date column for axis.

 

Edit: Or alternately create another column, to plot... Ex. Month + Working days.

Month+Working Days is not going to create duplicate values? How does that work?

 

Perfect!! thank you so much.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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