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

[priyankamane]

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.

[Eric_Zhang]

@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
    )
)

[priyankamane]

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?

[Eric_Zhang]

---

@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]

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

[Eric_Zhang · Accepted solution]

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]

tried. working. thanks.