Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
natabird3
Continued Contributor
Continued Contributor

Select sum of last data available in a period

Hello,

 

I am trying to do the following. I have monthly data example:

DateCountryFans
1/31/2017Canada513
1/31/2017Singapore6264
1/31/2017Australia1924
1/31/2017UK73094
1/31/2017Middle East25415
1/31/2017Italy3813
2/28/2017Canada33016
2/28/2017Singapore37163
2/28/2017Australia41309
2/28/2017UK45455
2/28/2017Middle East49602
2/28/2017Italy

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. 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@natabird3 

 

See if this works for you.

The model:Model.JPG

 

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:

Result.JPG

 

 

Here is the PBIX file for your reference: Fans Latest Month Selected 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

10 REPLIES 10
PaulDBrown
Community Champion
Community Champion

@natabird3 

 

See if this works for you.

The model:Model.JPG

 

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:

Result.JPG

 

 

Here is the PBIX file for your reference: Fans Latest Month Selected 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






natabird3
Continued Contributor
Continued Contributor

This worked for me thanks.

v-easonf-msft
Community Support
Community Support

Hi , @natabird3 

I made a demo with the data you provided here. Hope it meets your requirements.

pbix attach 

 

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:

06.png

 

And it shows as below:

05.png

 

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.

Anonymous
Not applicable

Hi, try this:

 

Misura Bella =

VAR MonthNumber = IF(ISFILTERED(Your_Table[Data].[Month]), CALCULATE(MAX(Your_Table[Data].[MonthNo])))

RETURN CALCULATE(SUM(Fans_Column), FILTER(Your_Table, Your_Table[Data].[MonthNo] = MonthNumber))
natabird3
Continued Contributor
Continued Contributor

Hello, i tried your solution however i got blank - nothin displaying with it:

fans measure =
VAR MonthNumber = IF(ISFILTERED('Calendar'[Date].[Month]),CALCULATE(MAX('Calendar'[Date].[MonthNo])))
RETURN CALCULATE(SUM('FB Data'[Fans]),FILTER('Calendar','Calendar'[Date].[MonthNo]=MonthNumber))
 
Also, tried @ solution, however once i put the measure as filter i am not able to select is = 1, the drop down doesnt work, so not sure why, because in your file it worked.
 
And for @Tad17 i tried the suggested topic however the solution in there didnt work for me neither, not sure what i am doing wrong as this should be quite simple to do in my understanding.
Anonymous
Not applicable

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:

 

Misura Bellaa =

VAR MonthNumber = IF(ISFILTERED(Your_Table[Date].[Month]), CALCULATE(MAX(Your_Table[Date].[MonthNo])), 1)

RETURN CALCULATE(SUM(Your_Table[Fans]), FILTER(Your_Table, Your_Table[Date].[MonthNo] = MonthNumber))
 
 
Now, when the slicer is clear (when you don't make a choice), the measure will display the sum of the fans of the first month number, so January. 
natabird3
Continued Contributor
Continued Contributor

hello, thanks for the quick reply. i actually have a date selected

I thought it might be because of my calendar measure:

 
Calendar =
  GENERATE (
    CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2020, 12, 30 ) ),
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month =  MONTH ( currentDay )
    VAR year =  YEAR ( currentDay )
    VAR month_name = FORMAT(DATE(year,month,1),"MMMM")
  RETURN   ROW (
    "day", day,
    "month", month,
    "month_name", month_name,
    "year", year )
  )
 
What do you think?
 

1.JPG

Anonymous
Not applicable

Another idea should be something like this. 

 

New Table 

Calendar = CALENDAR(DATE(2016,1,1),DATE(2020,12,31))
 
Put "Calendar" in the values of the slicer
 
New Measure 
Fans =
VAR Data = CALCULATE(MAX(Calendar[Date]))
VAR YearSelected = YEAR(Data)
VAR MonthNumberSelected = MONTH(Data)
RETURN CALCULATE(SUM(Fans_Column), FILTER(Your_Table, Your_Table[Data].[Year] = YearSelected), FILTER(Your_Table, Your_Table[Data].[MonthNo] = MonthNumberSelected))
Anonymous
Not applicable

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.

 

aaaaa.PNG

 

 

 

 

 

 

 

 

 

I think you should use this first column ([Date]) in the slicer and in the measure. 

Tad17
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.