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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Waw thanks, that looks exactly like what I need!

Anonymous
Not applicable

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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.