cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
natabird3 Helper V
Helper V

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

Accepted Solutions
Super User I
Super User I

Re: Select sum of last data available in a period

@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 




If this post has helped you, please give it a thumbs up!
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
Tad17 Solution Sage
Solution Sage

Re: Select sum of last data available in a period

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

rickymazz Resolver I
Resolver I

Re: Select sum of last data available in a period

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))
Microsoft v-easonf-msft
Microsoft

Re: Select sum of last data available in a period

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.

natabird3 Helper V
Helper V

Re: Select sum of last data available in a period

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.
rickymazz Resolver I
Resolver I

Re: Select sum of last data available in a period

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 Helper V
Helper V

Re: Select sum of last data available in a period

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

rickymazz Resolver I
Resolver I

Re: Select sum of last data available in a period

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. 

rickymazz Resolver I
Resolver I

Re: Select sum of last data available in a period

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))
Super User I
Super User I

Re: Select sum of last data available in a period

@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 




If this post has helped you, please give it a thumbs up!
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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors