Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am trying to reproduce this visual in Power BI.
I have 3 major tables. The Main table, let's call it Query1, a second table with Dates, let's call it a DateTable, and a Third Table called BudgetDrillMeters.
The Query1 table contains a Drill Date Column and another column for Meters Drilled for Each Date and Project Column.
The DateTable is a derived table that contains continuous dates from the minimum Drill Date to the maximum Drill Date using the CALENDER function.
The BudgetDrillMeters contains a Month Column and Year Column and the Budget Meters to be Drilled for each Month and Project Column.
I have created a Date Slicer from the DateTable to select a Start Date and an End Date to Filter the Data.
In the Visual, Under the Period Header, the first-row data value is the Date selected on the End Date of the Date Slicer.
The Actual is the accumulated Meters in Query1 corresponding to data under each Period. The Actual Calculations for the other Periods are done in reference to the date selected in the Date Slicer.
The Budget+AFE is the accumulated BudgetMeters corresponding to data under each period also in reference to the selected date.
Which Visual would you suggest to achieve such a dynamic row data and how do I go about this? I have tried using Table and Matrix Visual but none worked for me as I wanted especially with the selected date
Hello, I tackled something similar. My solution was to have a single select slicer available to the end-user where they could select either WTD, MTD, QTD, or YTD. Then, I had a measure with the following:
Previous Time Count =
VAR CurrentDate = LASTDATE(DateDim[Date])
VAR Selection = SELECTEDVALUE(TimeFrameSelector[Time Frame])
VAR Previous_Y = Year(MAX(DateDim[Date]))-1
VAR Previous_M = MONTH(MAX(DateDim[Date]))-1
VAR Previous_Q = QUARTER(MAX(DateDim[Date]))-1
VAR Previous_W = WEEKDAY(LASTDATE(DateDim[Date]),3)
RETURN
SWITCH(
TRUE(),
Selection = "Month to Date", CALCULATE(COUNT(Table1[Data]),FILTER(ALL(DateDim),MONTH(DateDim[Date])=Previous_M)),
Selection = "Quarter to Date", CALCULATE(COUNT(Table1[Data]),FILTER(ALL(DateDim),Quarter(DateDim[Date])=Previous_Q)),
Selection = "Week to Date", CALCULATE(COUNT(Table1[Data]),FILTER(ALL(DateDim),WEEKNUM(DateDim[Date])=Previous_W)),
Selection = "Year to Date", CALCULATE(COUNT(Table1[Data]),FILTER(ALL(DateDim),Year(DateDim[Date])=Previous_Y)),
"All"
)
Hope this gets you started in the right direction!
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.