Forum Discussion
Create dynamic graph and table based on a specific date
Hi Power BI guys,
Currently, I've been looking for an option to create a graph and a table based on specific date.
The only data we have is a products' groups , products, a budget and a date such is in the future.
Let's say:
| Group | Product | Budget | Lead date |
| GROUP1 | PRODUCT1 | 100 | 1/1/2024 |
| GROUP1 | PRODUCT2 | 60 | 1/1/2025 |
| GROUP1 | PRODUCT3 | 120 | 5/1/2023 |
| GROUP1 | PRODUCT4 | 20 | 4/1/2024 |
| GROUP1 | PRODUCT5 | 10 | 5/1/2024 |
| GROUP2 | PRODUCT1 | 100 | 1/1/2024 |
| GROUP2 | PRODUCT2 | 60 | 1/1/2025 |
| GROUP2 | PRODUCT3 | 120 | 5/1/2023 |
| GROUP2 | PRODUCT4 | 20 | 4/1/2024 |
| GROUP2 | PRODUCT5 | 10 | 5/1/2024 |
We are trying to build a table of how the budget decress a cross of the time based on the lead date.
To get how much will the product decress, we get the time difference in months from today to lead date...
Example:
Supossing that the current date is 8/1/2023 and the lead date is 1/1/2024 there is 4 months of difference and then dividing the budged / 4
So regarding with the table and the chart, we are trying to create a table like this:
| GROUP | PRODUCT | Time left Months | AUGUST 2023 | SEPTEMBER 2023 | OCTOBER 2023 | NOVEMBER 2023 | DECEMBER 2023 | JANUARY 2024 | FEBRARY 2024 | MARCH 2024 |
| GROUP1 | PRODUCT1 | 4 | 120 | 90 | 60 | 30 | 0 | 0 | 0 | 0 |
| GROUP1 | PRODUCT2 | 16 | 60 | 52.5 | 48.75 | 45 | 41.25 | 37.5 | 33.75 | 30 |
And the graph, basically a bar chart dividing in months by group x month.
My current conclusion is that I'd be better if a create data, but I wanted to know if there is any way to do it by dax (this will be publish as a project in Power BI Service...)
BTW, we have a calendar that we can use it, but currently I could not find any way to create a relation or use it
Any suggestion?
Thanks!
alfonsoalmada2 . Create a parameter and use that for minth you want Create a end date measure
end Date = eomonth(Max([Lead Date]), [Param measure])
The apply this approch,
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzMYou measure would be like
Sumx(filter (Table, Table[Lead Date]<= Max(date[Date]) && [end Date]>= Max(Date[Date])), divide([Budget],[Param measure]) )
You can use 4 in place of Param measure
Create an independent/disconnected date table
Date == Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(([DAte])) <7 , year(([DAte]))-1 ,year(([DAte])))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)
1 Reply
alfonsoalmada2 . Create a parameter and use that for minth you want Create a end date measure
end Date = eomonth(Max([Lead Date]), [Param measure])
The apply this approch,
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzMYou measure would be like
Sumx(filter (Table, Table[Lead Date]<= Max(date[Date]) && [end Date]>= Max(Date[Date])), divide([Budget],[Param measure]) )
You can use 4 in place of Param measure
Create an independent/disconnected date table
Date == Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(([DAte])) <7 , year(([DAte]))-1 ,year(([DAte])))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)