Filter the data based on latest Quarter in Bar chart by default

[rajendraongole1]

Hi

In report desinger i have visuals as below snapshot. but i am trying to get selected by default with latest quarter date which affects and filters the remaining visuals too.

is this possible to achieve?

Please suggest.

 

[v-juanli-msft · Accepted solution]

Hi @rajendraongole1 

As a workaround, you could create a visual to show the last quarter's data, then use this visual to reflect the another visual.

For example, the "last quarter's data" could update dynamically with my measure.

To get this, i create a date table which has a relationship with main table.

date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "year quarter", FORMAT (
        [Date],
        "yyyy-q"
    )
)

Create measures

last quarter =
VAR thisquarter =
    VALUE (
        FORMAT (
            TODAY (),
            "q"
        )
    )
RETURN
    IF (
        thisquarter = 1,
        YEAR (
            TODAY ()
        ) - 1 & "-" & 4,
        YEAR (
            TODAY ()
        ) & "-" & thisquarter - 1
    )

2yearsbefore =
VAR last2 =
    DATE ( YEAR (
        TODAY ()
    ) - 2, MONTH (
        TODAY ()
    ), DAY (
        TODAY ()
    ) )
VAR quarter2 =
    VALUE (
        FORMAT (
            last2,
            "q"
        )
    )
RETURN
    IF (
        quarter2 = 1,
        YEAR (
            TODAY ()
        ) - 3 & "-" & 4,
        YEAR (
            TODAY ()
        ) - 2 & "-" & quarter2
    )


period values = CALCULATE(SUM('Table'[value]),FILTER('date','date'[year quarter]>=[2yearsbefore]&&'date'[year quarter]<=[last quarter]))

selected lastquarter = CALCULATE(SUM('Table'[value]),FILTER('date','date'[year quarter]=[last quarter])
)

Please download my pbix for details.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @rajendraongole1 

As tested, i can only have default selected value on a slicer.

Please check if the following links help:

https://community.powerbi.com/t5/Desktop/Setting-a-bar-chart-to-a-default-selection/td-p/73284

Best Regards

Maggie

[amitchandak]

@rajendraongole1, Can you explain more about this. If you filter a qtr, how can other qtr have some data, Unless they are coming from some other dimension/date?

If there two dates joined on the same date and not working. You may have to use  two date dimensions.

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[rajendraongole1]

Hi Amit and maggie

Thanks for your responses.

Expecting, by default filtered the data based on latest quarter information on visuals along with that need to report last 2 years data, i have created one measure as below

able to get the 

measure_test =

Var currentdate= MAX(Date_FFA[period_Time_Stamp])

VAR Previousdate=DATE(YEAR(currentdate),MONTH(currentdate)-23,DAY(currentdate))

var result=

CALCULATE(DISTINCTCOUNT(table[USErid]),

FILTER(table,tablename[period_Time_Stamp]>=Previousdate && tablename[period_Time_Stamp]<=currentdate

))

return

result

 

from above able to acheive last 2 years data and count but the filter is not working.

 

any suggestions?