Help for using start date and end date issues

[Anonymous]

Hi,

       I am completely new to powerbi.. I want to show the next six months of data by using startdate and enddate in sampledata table..i am addding the screenshot for your reference

In that screenshot i have a sample data table but i want to show the datas by start and end date  i am adding the next six month table how my table wants to looks like also..

For exampe  i am explaining one category in that table.

 Above screenshot Books category have two start and end dates..

1-1-2020 to 12-7-2020 and 02-03-2020 to 31-05-2020 if i start may as the start month it should check the datas and add them in may like that wise i can do that all other datas in Dax queries... kindly check my screenshot as a refernce.... Could u help me on that...Is it possible or not?

Advance thanks to all,

[Anonymous]

Hi there,

Yes, what you want to accomplish is doable in Power BI.  However, you are showing a screenshot of Excel?!?  I'm new here as well and while I believe you can start Excel, I've always started in Power BI.  Please confirm you have Power BI. 

Essentially, you will need to create two tables.  One has all dates for the next few years and the product names:

Transactional Date Hierarcy = var _calendar = CALENDAR("1/1/2020","12/31/2025") return addcolumns(_calendar, "Today", Today(), "Product Name", allselected([PRODUCT NAME COLUMN]))

I'm not 100% on the syntax for getting the product name, but I think this is a good starting point.

The other table to add would be your Excel table. 

Add a relationship (far left icons) from the calendar table to your excel table by product name.  Excel should filter the calendar.  You would then create new measures (Table->New Measure) on the calendar table that helps to determine the "total" value for any given day.

Finally, you will then add a visualization (Insert->New Visual) and change that visual to a matrix under Visualizations.

Under Rows select Category.

Under Columns select Date (there will be a date hierachy in the table, best to use that column).

Under Values select whatever calculation you want to show from the calendar.

You can add filters to this visualization for the start and end date.

Sorry it is not a bit more detail.  Hope it helps...

[amitchandak · Accepted solution]

@Anonymous , not very clear to me. Check if this blog can help. It deals will start date end date and active between

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

hi @amitchandak ,

   Thanks for your reply but i didnt get that what i expected the output.

I explain what i want i have sample table in that table i have start date, end date, catgories and total 

for example i want to see next six months of data from today 

In my dataset have 

startdate           enddate       categories   total

02-02-2020      03-08-2020   books           20

03-04-2020      12-06-2020   books           10

I expected my output is

this month is June so June occurs between two dates what I given  in the example

categories     June   July   August  September   October   November

Books            30       20       20         0                   0                0

In this example June occurs for both the dates so we will add that 20+10 =30 and the july and august occurs only one so we will show that 20 ad 20 where september ,october and november has no dates so it should be 0 ....Like this kind of output i need for all the categories

[Anonymous]

@Anonymousyou'll need to provide more updates and screenshots from Power BI.  The link provided by @amitchandak was a pretty solid start.  Download it and tinker win the way they do the hire/terminated date it is what you need.