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

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

Reply
Frisbeeous
Frequent 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
Super User
Super User

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors