Last active
June 28, 2018 14:50
-
-
Save SchreiberLars/3d1068b780c962310f45e84ea8bfaf7a 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), | |
Geschaeftsmonat = | |
if AtuellerKalendermonat < Startmonatsnummer then | |
Text.PadStart(Number.ToText(13 - Startmonatsnummer + AtuellerKalendermonat), 2, "0") &"_"& Number.ToText(Date.Year(MyDate)-1) | |
else | |
Text.PadStart(Number.ToText(AtuellerKalendermonat - Offset), 2, "0") &"_"& Number.ToText(Date.Year(MyDate)) | |
in | |
"GM"& Geschaeftsmonat, | |
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 = "fnGeschaeftsMonatUndJahr", | |
Documentation.LongDescription = | |
"Gibt die Nummer und das Jahr des aktuellen Geschäftsmonats 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