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: 

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.

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
841 Views
0
1 ACCEPTED SOLUTION
Eric_Zhang
Employee
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
989 Views
0
1 REPLY 1
Eric_Zhang
Employee
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
990 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All