cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AmarK
Helper I
Helper I

How to create Date heirarchy in Existing table in Power BI

Hi All,

I am new POWER BI user. I need help in following kindly help.

I have imported Excel file to Power Bi and created some measures and Calculated Column for my requirement. i have difrrent months data and i am trying to show Stacked Bar of Total Overtime vs EstimatedOvertime with respect to each month. 

Excel file contains two months data from 20190501 to 20190615. For June i need to calculate remaining working days of the month excluding Saturdays and Sundays (from 16June 2019 to 30 June 2019). 

I have created new Calendar table using following formula,

Calendar = CALENDAR(DATE(2019,1,1),DATE(2040,12,31))

 

Regards,

AmarK

Is it possible to create Date Heirarchy inside our excel table?

if possible how to achieve it. Kindly help!

8 REPLIES 8
v-alq-msft
Community Support
Community Support

Hi, @AmarK 

 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

 

Best Regards

Allan

amitchandak
Super User
Super User

You can bring a date calendar from Excel. In case you have a working calendar there. You just need to mark that as of a date table in Power BI.

To create in power bi refer my file

https://www.dropbox.com/s/wrcyk5j66corvjg/Apr2Mar-Cal.pbix?dl=0

Or

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
RobbeVL
Impactful Individual
Impactful Individual

Hi Amar,

 

I would reccomend to keep using the Data table you specified.  It would not be usefull to keep a generated Hierarchy stored in your Excel source.

You could create a measure that calculates the remaining workingdays. 
If you would share a sample PBI or Excel file, I'd be happy to help you create this.

 

Regards,

 

Robbe

Hello RobbeVL,

Thank you so much for your quick response.

what i did is i have created New table called calendar. And now i have two table in my workspace,

1: Calendar which contains only Date.

2: Excel table which i have inported.

Now i want to calculate Total number of saturdays and sundays count by creating Measure or Calculated column in Second table i.e. In Excel table by using Calendar table for Dates.

I am able to calculate it Count in Calendar table by following DAX,

SaturadaysInMonth = if (weekday ('Calendar'[Date].[Date],1) =7,1,0)

SundaysInMonth = if (weekday ('Calendar'[Date].[Date],1) =1,1,0)

But i want this to be in Excel Table.

My tables are ,

 
 
 
 

PowerBi.PNG

If it is not possible then can we create Date Heirarchy in Excel Table as i have created in Calendar table from period 20190101 t0 20201231?

DateEmployeeIDDistributionPunchInTimePunchOutTimeNumber of SaturdaysNumber of SundaysTotalWeekends
2019060119939372005510
2019060219930374005510
201906031993037183418155510
201906041993037183618195510
201906051993037182918195510
201906061993037183118145510
201906071993037184518005510
2019060819930372005510
2019060919930374005510
201906101993037182318265510
201906111993037182217345510
201906121993037182320435510
201906131993037182220495510
201906141993037183618005510
2019061519930372005510

 

In above table Excel contains only Date, EmployeeId, Distribution, PunchInTime,PunchOutTime columns only.

I am trying to create NumberOfSaturdays, NumberOfSundays and TotalWeekends column as creating Measure or CAlculated column in same Excel table when i import this file to Power BI. Kindly Help.

Thanks in advance!

Reagrds,

Amar

v-alq-msft
Community Support
Community Support

Hi, @AmarK 

 

Based on your description, I created data to reproduce your scenario.

Table:

a1.png

 

Calendar :

 

 

Calendar = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

 

 

There is a one-to-one relationship between two tables.

 

You may create a calculated column and a measure as below.

 

 

Calculated column:
NewDate = VALUE(FORMAT('Calendar'[Date],"yyyymmdd"))

Measure:
NotWorkingDays = 
IF(
    SELECTEDVALUE('Table'[Date])<>BLANK(),
    CALCULATE(
        DISTINCTCOUNT('Calendar'[Date]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[NewDate]>=CALCULATE(MIN('Table'[Date]),ALL('Table'))&&
            'Calendar'[NewDate]<=CALCULATE(MAX('Table'[Date]),ALL('Table'))&&
            WEEKDAY('Calendar'[Date]) in {1,7}
        )
    )
)

 

 

 

There are five Saturday/Sundays between 6/1/2019 and 6/15/2019. Here is the result.

 a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hello Allan,

 

Thank you so much fro your quick response.

I am trying to find total weeknds. Here you have found weekends from 20190501 to 20190615 but i am trying to get whole month weekends count. The count of of NotWorkingDays should be 10.

 

The Date i am having in Excel Import Table in FormattedDate Column is of Data Type "Date/Time" and i tried to give one-to-one relationship between two tables but getting error as shown in attached image. Currently it is allowing Many-to-one realtionship only.Realtionship.pngRealtionshipError.PNG

Already i have created Calendar table and i tried creating Calculated Column (NewDate) in another table i.e. Excel imported table but i am unable to access Calendar table Date column in the Excel table.

 

Thanks in Advance.

 

Reagrds,

Amar 

v-alq-msft
Community Support
Community Support

Hi, @AmarK 

 

You may create a measure and calculated columns as below.

 

Calculated Column:
Month = MONTH('Calendar'[Date])
Year = YEAR('Calendar'[Date])
Month-Year = FORMAT('Calendar'[Date],"yyyymm" )

Measure:
Result = 
var _year = SELECTEDVALUE('Calendar'[Year])
var _month = SELECTEDVALUE('Calendar'[Month])
return
    CALCULATE(
        DISTINCTCOUNT('Calendar'[Date]),
        FILTER(
            ALL('Calendar'),
            YEAR('Calendar'[Date])=_year&&
            MONTH('Calendar'[Date])=_month&&
            WEEKDAY('Calendar'[Date]) in {1,7}
        )
    )

 

 

Result:

i1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-alq-msft
Community Support
Community Support

Hi, @AmarK 

 

If you want to calculate saturdays/sundays between 20190501 and 20190615, you may try the following measure.

Measure = 

    CALCULATE(
        DISTINCTCOUNT('Calendar'[Date]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[NewDate]>=20190501&&
            'Calendar'[NewDate]<=20190615&&
            WEEKDAY('Calendar'[Date]) in {1,7}
        )
    )

 

There is 13 saturdays/sundays between the date range.

f1.png

 

Best Regards

Allan

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.