Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrvamsidhar
Helper I
Helper I

Display through put data on current date

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?

2 ACCEPTED SOLUTIONS
GilbertQ
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

v-ljerr-msft
Employee
Employee

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. Smiley Happy

 

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

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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. Smiley Happy

 

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
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.