Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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] ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |