cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Creating daily report with MTD data

Hi,

 

I'm planning to create a daily report with data pulled out of BW.

 

The data would always be MTD, so every day I would save a file 2019.01.01, 2019.01.02 etc that contains all sales up to that date. 

Filename "2019.01.01" - Belgium 100$

Filename "2019.01.02" - Belgium 300$

Filename "2019.01.03" - Belgium 400$

 

What would be a good way to make a report that shows the daily values as 100$ for day 1, 200$ day 2 and 100$ day 3?

 

The dataset does not contain information on the date of the sale - I think I would need to calculate the difference between files based on the file names, but no idea how to actually do that. 

 

Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Creating daily report with MTD data

Hi @Anonymous,

 

If you do the option to connect to folder and use the name file to have a date column then based on that column you can use a measure similar to this one:

 

daily sales =
VAR date_selection =
    CALCULATE (
        MAX ( Sales[Date] );
        FILTER ( ALL ( Sales[Date] ); Sales[Date] < MAX ( Sales[Date] ) )
    )
RETURN
    SUM ( Sales[Sales] )
        - CALCULATE (
            SUM ( Sales[Sales] );
            FILTER ( ALL ( Sales[Date] ); Sales[Date] = date_selection )
        )

This will give you the result below:

 

daily sales.png

 

Regards,

MFelix

 


Regards

Miguel Felix


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

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Creating daily report with MTD data

Hi @Anonymous,

 

If you do the option to connect to folder and use the name file to have a date column then based on that column you can use a measure similar to this one:

 

daily sales =
VAR date_selection =
    CALCULATE (
        MAX ( Sales[Date] );
        FILTER ( ALL ( Sales[Date] ); Sales[Date] < MAX ( Sales[Date] ) )
    )
RETURN
    SUM ( Sales[Sales] )
        - CALCULATE (
            SUM ( Sales[Sales] );
            FILTER ( ALL ( Sales[Date] ); Sales[Date] = date_selection )
        )

This will give you the result below:

 

daily sales.png

 

Regards,

MFelix

 


Regards

Miguel Felix


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

Proud to be a Datanaut!




View solution in original post

Anonymous
Not applicable

Re: Creating daily report with MTD data

Waw thanks, that looks exactly like what I need!

Anonymous
Not applicable

Re: Creating daily report with MTD data

I am still not getting there because of the missing dates... how could I solve this?

Any idea why I need to use .[Day] whereas in the example it is not used?

 

 

 

daily sales =
VAR date_selection =
    CALCULATE (
        MAX ( Sales[Date].[Day] );
        FILTER ( ALL ( Sales[Date].[Day] ); Sales[Date].[Day] < MAX ( Sales[Date].[Day] ) )
    )
RETURN
    SUM ( Sales[Sales] )
        - CALCULATE (
            SUM ( Sales[Sales] );
            FILTER ( ALL ( Sales[Date].[Day] ); Sales[Date].[Day] = date_selection )
        )

 

 

 

Capture.PNG

Super User III
Super User III

Re: Creating daily report with MTD data

Hi @Anonymous,

 

You don't have any need to use the .Day part you can delete form your measure, in my example as you can see there are also missing dates but they don't show when put then on the table.

 

The .Day part is the time intelligence part kicking in since your date you have the option of Auto date your fields of date create a Time table behinh the scenes, so when you add the .Day to your measure it's getting the full days list.

 

Regards,

MFelix

 

 


Regards

Miguel Felix


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

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors