How to create Date heirarchy in Existing table in Power BI

[Anonymous]

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!

[v-alq-msft]

Hi, @Anonymous 

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]

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/

[RobbeVL]

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

[Anonymous]

Why don't you answer the question instead of saying "don't do this". If you don't want to answer the question, please don't post. It does not help anyone else who comes here with the same question to find you haven't answered it.

[Anonymous]

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 ,

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?

Date	EmployeeID	Distribution	PunchInTime	PunchOutTime	Number of Saturdays	Number of Sundays	TotalWeekends
20190601	1993937	2	0	0	5	5	10
20190602	1993037	4	0	0	5	5	10
20190603	1993037	1	834	1815	5	5	10
20190604	1993037	1	836	1819	5	5	10
20190605	1993037	1	829	1819	5	5	10
20190606	1993037	1	831	1814	5	5	10
20190607	1993037	1	845	1800	5	5	10
20190608	1993037	2	0	0	5	5	10
20190609	1993037	4	0	0	5	5	10
20190610	1993037	1	823	1826	5	5	10
20190611	1993037	1	822	1734	5	5	10
20190612	1993037	1	823	2043	5	5	10
20190613	1993037	1	822	2049	5	5	10
20190614	1993037	1	836	1800	5	5	10
20190615	1993037	2	0	0	5	5	10

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]

Hi, @Anonymous 

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

Table:

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.

 

Best Regards

Allan

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

[Anonymous]

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.

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]

Hi, @Anonymous 

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:

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]

Hi, @Anonymous 

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.

Best Regards

Allan