Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Calculate year to Month on selection of a month

‎11-15-2017 04:50 AM

Hi Team,

 

I have a satisfaction table in which there is data related to different months. However, I woul need to calculate the YTD for every year beginning from a particular fiscal year start (In this case April)

 

The count of case ID - YTD should be dynamic as in when i select month filter it should calculate the YTD till that particualar month.

For e.g: May month should be count of case ID should be- April+May,

Count of case ID for August should be April+May+June+July+August & so on.

 

The Data is going to be incremental every month so I would need help in creating a dynamic expression which can help me calculate the YTD.

 

I Tried looking into some examples for YTD but it did not help.

 

I am relatively new to POWERBI. Can someone help me on this as its a very important thin I use in my reports

 

Case number Survey received month AreaEstate officeQuarterCase closed dateCase handler HandlingOutcomeAddress Email address NameAnonymousMethod of survey YearFinancial year
2928784AprilSouthGrosvenor112/04/2017AMIAHVery satisfiedVery satisfied  Mrs S SinglehurstNoPhone20172017-18
2935987AprilSouthPimlico120/04/2017MTWYFORNeitherNot answered  Mr D P McCombeNoPhone20172017-18
2939469AprilSouthPimlico120/04/2017MTWYFORVery satisfiedVery satisfied  Miss A MaloumNoPhone20172017-18
2920484MayNorthMaida Vale126/04/2017MFAGANVery satisfiedVery satisfied  Mr GorgioNoPhone20172017-18
2980137MayWestBayswater127/04/2017DWILLIAVery dissatisfiedVery dissatisfied  Ms A MahalNoPhone20172017-18
3017129MayWestBayswater125/04/2017DWILLIAVery satisfiedFairly satisfied  Mr J JohnstoneYesPhone20172017-18
3018052MayWestWestbourne Park125/04/2017DWILLIAVery dissatisfiedVery dissatisfied  Miss M CarolanNoPhone20172017-18
2890803MayNorthMozart127/04/2017MFAGANVery satisfiedFairly dissatisfied  Miss P TindleNoPhone20172017-18
2877300JuneSouthGrosvenor123/05/2017RLARGANFairly satisfiedVery satisfied  Mrs K BibiNoPhone20172017-18
2961353JuneSouthChurchill Gardens125/05/2017MTWYFORVery satisfiedVery satisfied  Mrs P F SimeNoPhone20172017-18
3053080JuneSouthChurchill Gardens130/05/2017AMIAHFairly satisfiedNot answered  Mrs P J McKayNoPhone20172017-18
3062865JulyNorthMozart, Lydford & Avenue Gardens219/06/2017MFAGANNeitherFairly dissatisfied  Ms J A LingardNoPhone20172017-18
3051088JulyNorthMozart, Lydford & Avenue Gardens219/06/2017MFAGANFairly satisfiedFairly satisfied  Mr R HussainNoPhone20172017-18
3101481JulyCentralChurch Street Estate Office229/06/2017HELHROUVery satisfiedVery satisfied  Ms E EastNoPhone20172017-18
3101542JulyCentralChurch Street Estate Office228/06/2017HELHROUVery satisfiedFairly satisfied  Ms A Christine O'SullivanNoPhone20172017-18
3089501AugustNorthMozart, Lydford & Avenue Gardens231/07/2017MFAGANVery satisfiedVery satisfied  Ms Rita BegumNoPhone20172017-18
3114738AugustSouthLillington & Longmoore204/08/2017RSYLORIFairly satisfiedNot answered  Miss M Fernandes FerreiraNoPhone20172017-18
3122334AugustWestLittle Venice203/08/2017DWILLIAVery satisfiedFairly satisfied  Mrs P SmithNoPhone20172017-18
3085483SeptemberSouthGrosvenor Estate Office208/09/2017RSYLORINeitherFairly satisfied  Mrs E KhedarunNoPhone20172017-18
3150447SeptemberNorthMaida Vale Estate Office211/09/2017NSMITHVery satisfiedFairly satisfied  Mrs T Al DikirNoPhone20172017-18
3206796SeptemberWestWest Area Streets Office211/09/2017DWILLIAVery satisfiedFairly satisfied  Mr M ChavarroNoPhone20172017-18
3116309SeptemberSouthChurchill Gardens Estate Office218/09/2017MTWYFORVery satisfiedVery satisfied  Mr S JosephNoPhone20172017-18
3160365October CentralLisson Green Estate Office327/09/2017HELHROUVery satisfiedVery satisfied  Miss R YassinNoPhone20172017-18
3165968October SouthLillington and Longmoore Estate Office328/09/2017AMIAHFairly satisfiedVery satisfied  Mrs S SinghNoPhone20172017-18
3128168October CentralSt Johns Wood Estate Office305/10/2017KPEARCEVery satisfiedVery satisfied  Mr S AdsaniNoPhone20172017-18

 

 

Labels:
Message 1 of 2
965 Views
0
1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee

‎11-15-2017 06:22 PM

@Anonymous

In your particular case, you need a auxiliary table as below. Then create a many-to-one single direction relationship from your table to the below table.

Capture.PNG

 

Then create a measure as below.

YTD Per End Month =
COUNTAX (
    FILTER (
        ALLEXCEPT ( yourTable, yourTable[Financial year] ),
        RELATED ( Months[Sequence] ) <= MAX ( Months[Sequence] )
    ),
    yourTable[Name]
)

YTD.gif

See the attached pbix file.

 

View solution in original post

YTD.zip
Message 2 of 2
1,113 Views
0
1 REPLY 1
Eric_Zhang
Microsoft Employee
Microsoft Employee

‎11-15-2017 06:22 PM

@Anonymous

In your particular case, you need a auxiliary table as below. Then create a many-to-one single direction relationship from your table to the below table.

Capture.PNG

 

Then create a measure as below.

YTD Per End Month =
COUNTAX (
    FILTER (
        ALLEXCEPT ( yourTable, yourTable[Financial year] ),
        RELATED ( Months[Sequence] ) <= MAX ( Months[Sequence] )
    ),
    yourTable[Name]
)

YTD.gif

See the attached pbix file.

 

YTD.zip
Message 2 of 2
1,114 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All