Display through put data on current date

[mrvamsidhar]

Hello, Let's say I am having sales data of last 6 Months by Day.I want two generate two reports

(1) Let's say current date is 20 April. I want to generate report sales of last 6 Months from 1-20. For tomorrow, My report should display data from 1-21 (Nov,Dec,Jan,feb,Mar,April)

(2) Also, I want to generate Last 6 months on the same current day. Lets say today date is April 20, I want to generate report on Nov 20, Dec 20, Jan 20, Feb 20, Mar 20, April 20. For next day, again my report has to update Nov 21, Dec 21, Jan 21, Feb 21, Mar 21, April 21.

Any inputs?

[GilbertQ · Accepted solution]

Hi @mrvamsidhar

The way that I would slove this is by first ensuring I have a date table created and used.

Then what I would do is to create a new column for your first requirement in the Query Editor. And in this column you could then calculate if it falls into the same period as you specified. If it does in this column I would then create it with a name of "Last 6 Months by Day" for the true portion and "Not Last 6 Months" for the false.

Again I would create another column in the Query Editor based on your second requirement, and go through a similar process.

Then you could use each of those columns in a slicer to show the data you require?

[v-ljerr-msft · Accepted solution]

Hi @mrvamsidhar,

In addition, you can also use DAX to add two calculate columns to indicate if the sale date is current date or falls into Date Between Day 1 and Current Date, then use them as Slicers in your report to get your expected result. The formulas below are for your reference. 

Date Between Day 1 and Current Date =
IF (
    DAY ( Sales[Date] ) <= DAY ( TODAY () ),
    "Date Between Day 1 and Current Date",
    "Others"
)

Current Date = IF ( DAY ( Sales[Date] ) = DAY ( TODAY () ), "Current Date", "Others" )

Regards

[GilbertQ · Accepted solution]

Hi @mrvamsidhar

The way that I would slove this is by first ensuring I have a date table created and used.

Then what I would do is to create a new column for your first requirement in the Query Editor. And in this column you could then calculate if it falls into the same period as you specified. If it does in this column I would then create it with a name of "Last 6 Months by Day" for the true portion and "Not Last 6 Months" for the false.

Again I would create another column in the Query Editor based on your second requirement, and go through a similar process.

Then you could use each of those columns in a slicer to show the data you require?