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.
I am trying to create a Line and Bar chart that will represent a running total from 2019(Line) and 2020(Bar) over the same months. However, the 2019 data from Jan-Aug is displayed as the running total from Dec 2019.
Here are my formulas and screeshots below:
2019 = CALCULATE(
COUNT(PS_Data[Employee ID]),
FILTER(
ALLSELECTED(PS_Data),
PS_Data[Late/Not Late] = "Early" &&
PS_Data[Action Date]<= MAX(PS_Data[Action Date])&& PS_Data[Action Date].[Year] = 2019))
2020 = CALCULATE(
COUNT(PS_Data[Employee ID]),
FILTER(
ALLSELECTED(PS_Data),
PS_Data[Late/Not Late] = "Early" &&
PS_Data[Action Date]<= MAX(PS_Data[Action Date])&& PS_Data[Action Date].[Year] = 2020))
Solved! Go to Solution.
Hi @nawlins76 ,
You can modify your [2019] measure like this:
2019 =
CALCULATE (
COUNT ( PS_Data[Employee ID] ),
FILTER (
ALL ( PS_Data ),
'PS_Data'[Late/Not Late] = "Early"
&& 'PS_Data'[Action Date]
<= MAXX (
FILTER ( 'PS_Data', 'PS_Data'[Action Date].[Year] = 2019 ),
[Action Date]
)
)
)
Atttached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@nawlins76 , if you date or create a new column with help from this video -https://www.youtube.com/watch?v=cJqgphIHXz8
They try time intelligence and date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value = CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
previous month value = CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
Next to next month value = CALCULATE(sum('table'[total hours value]),nextmonth(dateadd('Date'[Date],1,MONTH)))
previous to previous month value = CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))
Last year same month value = CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-12,MONTH)))
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
@amitchandak Thank you, but using the date table isn't giving the results I'm looking for... essentially, I need to 2019 data to show as Null or Blank or even 0 during the 2020 months. see chart below for desired output:
Hi @nawlins76 ,
You can modify your [2019] measure like this:
2019 =
CALCULATE (
COUNT ( PS_Data[Employee ID] ),
FILTER (
ALL ( PS_Data ),
'PS_Data'[Late/Not Late] = "Early"
&& 'PS_Data'[Action Date]
<= MAXX (
FILTER ( 'PS_Data', 'PS_Data'[Action Date].[Year] = 2019 ),
[Action Date]
)
)
)
Atttached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thank you VERY much! This solved my issue. Just had to change the ALL to ALLSELECTED.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |