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 t...
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

     

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

     

     

     

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

      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