Create custom text based date on X axis

[SUMESHKUMAR22]

Hi BI folks,

Im looking our for a ideal / best approaches to handle below type of visual requirement in purely Power BI or SQL code. How to build the data view for this?

 

In the mockup image, its Showing last 2-weeks dynamic (2 bars) and the base case (black bar) is constant value.

For a KPI, Basically showing dynamic last 2 weeks with a black constant bar & the circle dot changing as per the last week value more than base case -(black bar) .

Please any reference/workaround would be appreciated.

Thanks!

[ryan_mayu]

do you have a date slicer that will change the date or if the data were updated, this visual always display last 2 weeks data？

what's more, could you pls provide some sample data? how we get the base case value?

[SUMESHKUMAR22]

hi Ryan,

- Always display last 2 weeks (sun-sat) dynamically (looking into it today- it will be 30 mar - 5 mar (Previous week)& 6 - 12 mar (last week)). Current week will not be included because its has not completed the full 7 days timeframe.

- for base case, we can enter sample data using datatable in power bi (kpi name & base value) as its a constant value.

thanks in advance!

[ryan_mayu · Accepted solution]

@SUMESHKUMAR22 

since we don't have the fact table, i can only create the date table for testing.

pls see the attachment below

[SUMESHKUMAR22]

Hi Team,

Currently my fact model looks like below:

date, kpi name, few dimensions, Units (sum/divide), value , value 1.

my generic calculation: if the Units = 'sum' then sum(value) else sum(value)/ sum(value1)

Also for the above case of scenario should I create a disconnected table approach?

Help me with the steps please. Thanks!