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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Resolver I
Resolver I

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors