Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
From the table below, I'd like to calculate the total sales for the coming month, in the previous year, and display it on a card visual, as a single value.
So, for example, in May 2019, the card will display the total of June 2018
In June 2019, it will display the total of July 2018. And so on.
Is that possible, somehow? Thank you.
Solved! Go to Solution.
@EVEAdmin try following measure, and make it is best to make filter direction to single
Sum 11 = TOTALMTD( SUM( Sales1[Sales] ), DATEADD( c[Date],-11,MONTH))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @EVEAdmin ,
You should create a similar measure to the one below:
PY month ahead = CALCULATE ( TOTALMTD ( SUM ( Sales[Total] ); Sales[Invoice Date].[Date] ); DATEADD ( Sales[Invoice Date].[Date]; -11; MONTH ) )
I would advice to create a calendar table that links to your invoice date and then create the following measure instead:
PY month ahead = CALCULATE ( TOTALMTD ( SUM ( Sales[Total] ); 'Calendar'[Date] ); DATEADD ( 'Calendar'[Date]; -11; MONTH ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix many thanks
I went for option 2), but I must have done something wrong.
I created a Calendar table as below
Calendar = CALENDAR(MINX(Sales,Sales[InvoiceDate]),MAXX(Sales,Sales[InvoiceDate]))
and I linked it to the sales table as below
However, when I drag the measure to a card visual, it returns blank.
Hi @EVEAdmin ,
You need to take the bidirectionality between tables.
On the Relationship editor chose one to many and single on cross filter, One side will be the calendar and many the invoices table, should work as expected.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI suspect there are issues with dates in the original data source. Will come back on this when I sort out the Calendar table
@EVEAdmin try following measure, and make it is best to make filter direction to single
Sum 11 = TOTALMTD( SUM( Sales1[Sales] ), DATEADD( c[Date],-11,MONTH))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |