Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Frisbeeous
Regular Visitor

How do I get a Visual Like this

I am trying to reproduce this visual in Power BI.
Periods.png

 

 

 

 

 

 

 

 

 

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

 

1 REPLY 1
ExcelMonke
Helper III
Helper III

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors