cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
priyankamane Frequent Visitor
Frequent Visitor

by default latest 30 days compare with last year resp. fiscal days with previous next 30 days option

I want to compare sales amount by 30 days with simple "line and stacked column chart" visual where fiscal days will get plotted on x axis and sales amount of current year in bar chart on y axis and sales amount in line chart on y axis.

 

by default it should get display these 30 days: from (today - 30) to (today)

 

there should be option to filter these days from previous 30 days or next 30 days.

 

Example: say today is 4 August 2017. so by default visual will show sales from 6 July 2017 to 4 August 2017. say these days fiscal day number is 190 to 220. and last years same fiscal day numbers date can be different but will plot 190 to 220 fiscal days on x axis and current and last year sales of these days in bar and line chart.

 

Now there should be some filter using which I can swith between these days. if I say previous then it should load 159 to189 fiscal days and their sales and so on.

 

Is this possible in Power BI. if yes then how?

 

Currently I have implemented this report in such a way that it will load data by fiscal months. 1 to 12 or 13 numbers I have put in slicer which are actually a fiscal months. and I am filtering using this slicer.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: by default latest 30 days compare with last year resp. fiscal days with previous next 30 days op

Then try

 

slicer =
SWITCH (
    TRUE (),
    'calendar'[year]
        = YEAR ( TODAY () ) - 1, "old data",
    TODAY () - 'calendar'[Date]
        <= 60
        && TODAY () - 'calendar'[Date]
            > 30, "previous 30 days",
    TODAY () - 'calendar'[Date]
        <= 30, "current 30 days",
    CONCATENATE (
        CONCATENATE (
            "previous x",
            CEILING (
                (
                    INT ( TODAY () - DATE ( YEAR ( 'calendar'[Date] ), 1, 1 ) )
                        + 1
                        - 'calendar'[dayNo]
                )
                    / 30,
                1
            )
                - 1
        ),
        " 30-days"
    )
)
6 REPLIES 6
Moderator Eric_Zhang
Moderator

Re: by default latest 30 days compare with last year resp. fiscal days with previous next 30 days op

@priyankamane

I think you can play some tricks on the calendar table by creating a slicer category column and create a measure specific for the same days of last year. See more details in the attached pbix file.

 

See

 

//create in calendar table
slicerColumn =
SWITCH (
    TRUE (),
    TODAY () - 'calendar'[Date]
        > 60"old data",
    TODAY () - 'calendar'[Date]
        <= 60
        && TODAY () - 'calendar'[Date]
            > 30"previous 30 days",
    "current 30 days"
)

//measure
value for the same day of last year = CALCULATE ( SUM ( yourTable[value] ), FILTER ( ALL ( 'calendar' ), 'calendar'[dayNo] = MAX ( 'calendar'[dayNo] ) && 'calendar'[year] = MAX ( 'calendar'[year] ) - 1 ) )

Capture.PNGCapture.PNG

 

Highlighted
priyankamane Frequent Visitor
Frequent Visitor

Re: by default latest 30 days compare with last year resp. fiscal days with previous next 30 days op

Thank you.

for loading current 30 days data, this solution is useful.

but then how can we devide all available data in 30 days with respect to today.

 

I mean today date will change every day, And I need a custom column which will be used as a slicer and devide itself in 30 days starting from today and update the same logic every day. what can be dax formula for such logic?

 

Moderator Eric_Zhang
Moderator

Re: by default latest 30 days compare with last year resp. fiscal days with previous next 30 days op


@priyankamane wrote:

Thank you.

for loading current 30 days data, this solution is useful.

but then how can we devide all available data in 30 days with respect to today.

 

I mean today date will change every day, And I need a custom column which will be used as a slicer and devide itself in 30 days starting from today and update the same logic every day. what can be dax formula for such logic?

 


@priyankamane

I don't quite get your point. The current 30 days in my solution in dynamic, for today(7th Aug, 2017), it refers to 8th Jul ~ 7th Aug. For tommorow, it refers to 9th Jul ~ 8th Aug.

priyankamane Frequent Visitor
Frequent Visitor

Re: by default latest 30 days compare with last year resp. fiscal days with previous next 30 days op

yes. your solution is correct for current 30 days data and it is dynamic also. So, 50% problem is solved. 

 

Now, My point is first 2 slicer options are correct:

1. current 30 days

2. previous 30 days

 

but 3rd one which is 3. Old Data

contains all old data...I want all available data devided by 30 days...means if I am having whole year daily data...then this slicer should contain 12 to 13 options. I want this dynamically starting from today date value.

 

Thanks

Moderator Eric_Zhang
Moderator

Re: by default latest 30 days compare with last year resp. fiscal days with previous next 30 days op

Then try

 

slicer =
SWITCH (
    TRUE (),
    'calendar'[year]
        = YEAR ( TODAY () ) - 1, "old data",
    TODAY () - 'calendar'[Date]
        <= 60
        && TODAY () - 'calendar'[Date]
            > 30, "previous 30 days",
    TODAY () - 'calendar'[Date]
        <= 30, "current 30 days",
    CONCATENATE (
        CONCATENATE (
            "previous x",
            CEILING (
                (
                    INT ( TODAY () - DATE ( YEAR ( 'calendar'[Date] ), 1, 1 ) )
                        + 1
                        - 'calendar'[dayNo]
                )
                    / 30,
                1
            )
                - 1
        ),
        " 30-days"
    )
)
priyankamane Frequent Visitor
Frequent Visitor

Re: by default latest 30 days compare with last year resp. fiscal days with previous next 30 days op

tried. working. thanks.