Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to do the following. I have monthly data example:
Date | Country | Fans |
1/31/2017 | Canada | 513 |
1/31/2017 | Singapore | 6264 |
1/31/2017 | Australia | 1924 |
1/31/2017 | UK | 73094 |
1/31/2017 | Middle East | 25415 |
1/31/2017 | Italy | 3813 |
2/28/2017 | Canada | 33016 |
2/28/2017 | Singapore | 37163 |
2/28/2017 | Australia | 41309 |
2/28/2017 | UK | 45455 |
2/28/2017 | Middle East | 49602 |
2/28/2017 | Italy | 53748 |
What i would like to do with this is if i select a month to get the sum of all fans for all countries. However if i select a longer period Jan and Feb together i do not want to sum as that would duplicate my values, instead i would want to display only the sum of the last month in the period selected. Now my question is, is this possible in Power Bi? If so any help in making it would be appreciated, thanks in advance.
Solved! Go to Solution.
See if this works for you.
The model:
1) calculate last "date" selected:
Last Date selected = CALCULATE(MAX('Data Table'[Date]); ALLSELECTED('Calendar'))
2) IF statement to show only latest month's fan count:
Calc Fans Latest = IF(MAX('Calendar'[Date]) = [Last Date selected]; [Sum of Fans]; BLANK())
3) Final Meaure to display in visual with date filter context:
Fans Latest Month Sel =
VAR calc = SUMX('Calendar'; [Calc Fans Latest])
RETURN
IF(ISINSCOPE('Calendar'[Month Year]); [Calc Fans Latest]; calc)
4) Measure to display in visual with Country filter context (the values displayed refer to the last month a particular country has data for. - in the screenshot below, Italy is the only country with data for March; all the rest show Feb figures):
Fans in Latest Month by country =
VAR calc = SUMX('Country'; [Fans in Latest Month])
RETURN
IF(ISINSCOPE('Country'[Country]); [Fans in Latest Month]; calc)
Which gets you this:
Here is the PBIX file for your reference: Fans Latest Month Selected
Proud to be a Super User!
Paul on Linkedin.
See if this works for you.
The model:
1) calculate last "date" selected:
Last Date selected = CALCULATE(MAX('Data Table'[Date]); ALLSELECTED('Calendar'))
2) IF statement to show only latest month's fan count:
Calc Fans Latest = IF(MAX('Calendar'[Date]) = [Last Date selected]; [Sum of Fans]; BLANK())
3) Final Meaure to display in visual with date filter context:
Fans Latest Month Sel =
VAR calc = SUMX('Calendar'; [Calc Fans Latest])
RETURN
IF(ISINSCOPE('Calendar'[Month Year]); [Calc Fans Latest]; calc)
4) Measure to display in visual with Country filter context (the values displayed refer to the last month a particular country has data for. - in the screenshot below, Italy is the only country with data for March; all the rest show Feb figures):
Fans in Latest Month by country =
VAR calc = SUMX('Country'; [Fans in Latest Month])
RETURN
IF(ISINSCOPE('Country'[Country]); [Fans in Latest Month]; calc)
Which gets you this:
Here is the PBIX file for your reference: Fans Latest Month Selected
Proud to be a Super User!
Paul on Linkedin.
This worked for me thanks.
Hi , @natabird3
I made a demo with the data you provided here. Hope it meets your requirements.
Create a measure "visual control" :
visual control = IF( CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table')) in FILTERS('Table'[Date]),1,-1)
Add the measure to the visual filter:
And it shows as below:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, try this:
Hello, i tried your solution however i got blank - nothin displaying with it:
Hi, nothing is displayed with this measure because probably you didn't select any month by the month slicer.
So, if you want to display a result despite the slicer isn't selected, you should make a change to the measure like this:
hello, thanks for the quick reply. i actually have a date selected
I thought it might be because of my calendar measure:
Another idea should be something like this.
New Table
Well, if you use these calendar months in the slicer probably you won't receive any result because there's no relationship between these calendar months and the months oh your dataset.
I think you should use this first column ([Date]) in the slicer and in the measure.
Hey @natabird3
Check out this thread and let me know if it helps: https://community.powerbi.com/t5/Developer/Show-total-only-last-month/td-p/88514
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |