Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey, guys
Could you please help me (kind of a begginer here). I want to make a matrix with cumulative daily sales grouped by day of month and organized in monthly columns 🙂
I will try to explain, imagine this:
So, at day 2 of January we would have $35 (which is day 1 plus day 2 of january).
So, at day 2 of February we would have $12(which is day 1 plus day 2 of february).
I'm trying to make a matrix like this:
Currently the best code so far was:
Cumulative = CALCULATE ( SUM ( 'Daily - by Índices'[Daily Revenue] ), FILTER ( ALLSELECTED('Daily - by Índices'[Data]), 'Daily - by Índices'[Data] <= MAX ('Daily - by Índices'[Data] ) ) )
However, I was only able to create such a matrix with rows like "DATE" without hierarchy (each day appears in a single row in a endless table)
I would like to reinforce that apparently the code above WORKS, however I want to group rows by day of month (not have a single date in each row).
Any ideas?
Thanks in advance.
-Matt
Solved! Go to Solution.
Hey,
I created a new measure
Amount Month ToDate = CALCULATE( SUM('FactWithDates'[Amount]) ,FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date])) && MONTH('Calendar'[Date]) = MONTH(MAX('Calendar'[Date])) ) )
and updated the ReportPage in the file I mentioned above, redownload please.
I guess this measure creates what your are looking for:
Be aware that I also checked "Show items with no data" for the columns and rows.
Regards
Hi @mateus_sjc,
For your requirement, you want to get month to date total, right?
If this is a case, you can create a measure to sum of sale by filter on year,month and date.
After above steps, creata a matrix visual, switch date to hierarchy mode, day part to row, month to column filed, drag above measure to value field.
Result = var current_date=MAX('Fact'[Date]) return SUMX(FILTER(ALLSELECTED('Fact'),[Date].[Year]=YEAR(current_date)&&[Date].[MonthNo]=MONTH(current_date)&&[Date]<=current_date),[Amount])
Compare:
Regards,
Xiaoxin Sheng
Hey,
maybe this is what you are looking for, here is PBI file, have a look at the page "Matrix Visual":
This is the Measure "Amount ToDate" I'm using in the Matrix
Amount ToDate = IF(NOT(ISBLANK(SUM('FactWithDates'[Amount]))) ,CALCULATE( SUM('FactWithDates'[Amount]) ,FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date]))) ) )
Maybe this helps
Regards
Hm... no, sorry
I don't wanna cross information between months. I'm trying to reproduce the exaclty same table that I posted
Let me try this: "I wish to be able to compare results between months, so I need cumulative results from each month."
Rows with days of months and each month with your on information and cumulative.
Imagine the following results of 4 sales during the period:
What I expect from these results is exactly a matrix like this:
So:
Note that from day 1 - 4 January we only have $10, which is from that one sale.
Then, from day 5-31 we have $28 repeateadly (which is $10 from first sale + $18 from the last)
Also:
The code that I posted works, actually. However, only with DATE, not DATE HIERARCHY. So, the result is a long table with each date in a different row - which sucks badly to compare results.
I wish to be able to compare results between months, so I need cumulative results from each month.
I think now I'm better expressing myself 🙂
Regards,
Hey,
I created a new measure
Amount Month ToDate = CALCULATE( SUM('FactWithDates'[Amount]) ,FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date])) && MONTH('Calendar'[Date]) = MONTH(MAX('Calendar'[Date])) ) )
and updated the ReportPage in the file I mentioned above, redownload please.
I guess this measure creates what your are looking for:
Be aware that I also checked "Show items with no data" for the columns and rows.
Regards
Hello ,maybe the following code will help you :
Cumulative = CALCULATE ( SUM ( 'Daily - by Índices'[Daily Revenue] ), DATESMTD( 'Daily - by Índices'[Data] ) )
Hello,the following code may work:
Cumulative = CALCULATE ( SUM ( 'Daily - by Índices'[Daily Revenue] ), DATESMTD( 'Daily - by Índices'[Data] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |