Forum Discussion
Help for using start date and end date issues
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 , not very clear to me. Check if this blog can help. It deals will start date end date and active between
4 Replies
- amitchandak
Super User
Anonymous , not very clear to me. Check if this blog can help. It deals will start date end date and active between
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
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.
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...