Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!!

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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:

     

     

    Regards,

    MFelix

     

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    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:

     

     

    Regards,

    MFelix

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Waw thanks, that looks exactly like what I need!

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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