Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWaw thanks, that looks exactly like what I need!
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 )
)
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |