Last active
June 28, 2018 14:58
-
-
Save SchreiberLars/59ba077fcc7e6f84e1c594c961c1cdbc to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
fn = (MyDate as date, StartMonat as text) => | |
let | |
Startmonatsnummer = | |
List.PositionOf( | |
{"januar", "februar","märz","april","mai","juni", "juli", "august", "september", "oktober", "november", "dezember"}, | |
Text.Lower(StartMonat) | |
) + 1, | |
Offset = Startmonatsnummer -1, | |
AtuellerKalendermonat = Date.Month(MyDate), | |
GeschaeftsQuartal = | |
if AtuellerKalendermonat < Startmonatsnummer then | |
"GQ"&Number.ToText(Number.RoundUp((13 - Startmonatsnummer + AtuellerKalendermonat) / 3, 0)) &"_"& Number.ToText(Date.Year(MyDate)-1) | |
else | |
"GQ"& Number.ToText(Number.RoundUp((AtuellerKalendermonat - Offset) / 3, 0)) &"_"& Number.ToText(Date.Year(MyDate)) | |
in | |
GeschaeftsQuartal, | |
fnType = type function( | |
MyDate as (type date meta [Documentation.LongDescription="Hier wird das zu prüfende Datum (meist aus einer Datumsspalte) übergeben."]), | |
Startmonat as (type text meta | |
[ | |
Documentation.LongDescription="Hier wird der Monat als Text eingegeben, mit dem das Geschäftsjahr beginnt", | |
Documentation.AllowedValues = {"Januar", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"} | |
] | |
) | |
) as text meta [ | |
Documentation.Name = "fnGeschaeftsQuartalUndJahr", | |
Documentation.LongDescription = | |
"Gibt die Nummer des aktuellen Geschäftsquartals und des Geschäftsjahres wieder. Dies ist sinnvoll bei von Kalenderjahr abweichenden Geschäftsjahren", | |
Documentation.Author = "Lars Schreiber, www.ssbi-blog.de" | |
] | |
in | |
Value.ReplaceType(fn, fnType) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment